|
|
Posted by Jerim79 on 08/07/07 15:05
On Aug 7, 9:26 am, Boris Stumm <st...@informatik.uni-kl.de> wrote:
> Jerim79 wrote:
> > I am trying to create a search system for an existing database.
> > Because of the way the database is setup, I need to traverse three
> > different tables, gathering information:
>
> > 1) Go into table1 and lookup show_id by show name. (For instance,
> > let's say I am looking for the Mickey Mouse show. I look it up and
> > find that it's show_id is 6)
>
> > 2) Go into table2 and lookup all attendee_ids for show_id 6. (For
> > instance, I lookup show_id 6 and find 5 attendees. 1, 2, 3, 4 and 5
> > are the attendee_id's)
>
> > 3) Go into table3 and lookup attendee names, from the 5 attendee_ids.
> > (For instance, I lookup 1, 2, 3, 4 and 5 to find their names are Jane,
> > Alice, Tom, Peter and Greg.)
>
> > I am not free to adjust the tables, so I have to stick with what is
> > there. There may be an SQL statement that could do all this in one
> > command, but I am no SQL expert.
>
> select
> table3.attendee_name
> from
> table1, table2, table3
> where
> table1.show_name = 'Mickey Mouse' and
> table1.show_id = table2.show_id and
> table2.attendee_id = table3.attendee_id
>
> Note that the default String enclosing characters in MySQL are not
> standard, i.e., not '', so you have to change that.
That worked like a charm. Thank you very much. I took SQL back in
college, and use it from time to time, but never really have a need
for it 90% of the time. Your example immediately made sense.
Navigation:
[Reply to this message]
|