SEO

#1 (**permalink**) 02-28-2008, 10:23 AM

I am wondering if anyone can help me with getting either a complex SQL
query, or several simple ones that would help me accomplish the
following:

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 work with what is
there. Unfortunately, there is no common field throughout all tables.
Table1 and table2 share show_id. Table2 and table3 share attendee_id.
I just need to end up with an array that contains all the Jane, Alice,
Tom, Peter and Greg. Any help would be appreciated.

#2 (**permalink**) 02-28-2008, 10:23 AM

On Aug 7, 10:12 am, Jerim79 <my...@hotmail.com> wrote:
> I am wondering if anyone can help me with getting either a complex SQL
> query, or several simple ones that would help me accomplish the
> following:
>
> 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 work with what is
> there. Unfortunately, there is no common field throughout all tables.
> Table1 and table2 share show_id. Table2 and table3 share attendee_id.
> I just need to end up with an array that contains all the Jane, Alice,
> Tom, Peter and Greg. Any help would be appreciated.

You just join them together on the fields that are common:

select t3.name attendeeName
from table1 t1
join table2 t2
on t1.show_id = t2.show_id
join table3 t3
on t2.attendee_id = t3.attendee_id
where t1.name = 'Mickey Mouse Show'

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Three tables