Posted by Boris Stumm on 08/07/07 14:26

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.

[Back to original message]