multitable SELECT question

[schwank]

Been a bit since I wrote any difficult SQL...so bear with me!

I am storing one record in an events table like so:
prim_key id
int attendee_id1
int attendee_id2
int attendee_id3
...

the attendee table is:
prim_key id
varchar name
...

I would like to return a single dataset containing all of the fields from the events table, but instead of returning the attendee_idX fields, I would like the corresponding attendee.name field...

my sql abilities are just not that great... can anyone help me out? it would be much appreciated.

thanks, eric

 

[MeanGreen]

Within your attendee table do you store the attendee_idx for reference? If so here is the quick and dirty solution:

select events.*,att.name
from events join attendee att
on events.attendee_idx = att.attendee_idx

If you have set up your events and attendee link to be the prim_key then you might be able to do this:

select events.prim_key,att.name
from events join attendee att
on events.prim_key = att.prim_key
group by events.prim_key

Good Luck

 

[rac2]

SELECT a1.name,a2.name,a3.name,e.activity
FROM events e
LEFT JOIN attendee a1 ON e.attendee_id1 = a1.attendee_id
LEFT JOIN attendee a2 ON e.attendee_id2 = a2.attendee_id
LEFT JOIN attendee a3 ON e.attendee_id3 = a3.attendee_id

The LEFT JOIN insures that you get a row for every event in case there might not be three attendees for some events.
You will need to specify the columns from the events table instead of using e.* because you probably don't want to see the columns with attendee ids.

 

[schwank]

This would work just fine... but I would like to get this result set:

int event_id
varchar() name1
varchar() name2
varchar() name3
...

All of the joins I have tried give me crazy result sets.
Your result set for

select events.*,att.name
from events join attendee att
on events.attendee_idx = att.attendee_idx

would return only one name field for a given event, I want to fill all 3 attendee name fields. Note the idX above is supposed to have X replaced by a # from 1-3. Any better clarification?

 

[schwank]

Awesome rac2...

I really appreciate it! You just made my life quite a bit easier...

Thanks.

 

[SQLSister]

I can't stand it, I have to comment. While I am aware that you are probably stuck with this design if this is an existing production database, if you are designing a new database, please avoid this structure. You should have three tables - an Event table with the details about the event, an EventAttendees table with the list of who is attending a particular event (join on eventID to events tables and attendee id to Attendee), and an Attendee table with the names, addresses etc, about attendees. This way you do not have to change the structure of the event table every time you have a larger number of attendees. This can get ridiculous; I just had to convert an Access table (for another organization) that actually had 133 separate fields for telephone ports, you can imagine what a nightmare writing queries on this would be! Anytime you have a one to many relationship, they should be in separate tables.