JOIN three tables

[danb]

Hi,

I've got three tables:

tblEvent
--------
Id (PK) | Event_Name



tblDelegate
--------
Id (PK) | Delegate_Name



tblBooking
--------
Id (PK) | Event_Id (FK) | Delegate_Id (FK)





I need to retrieve a recordset with the following information:

Booking Id | Event_Name | Delegate_Name


Can anyone see how to do a SELECT statement to do this?

ANy help would be great, thanks!

 

[gromit]

Hi Dan -

Try something along these lines:

SELECT tblBooking.ID, tblEvent.Event_Name, tblDelegate.Delegate_Name
FROM (tblBooking INNER JOIN tblDelegate ON tblBooking.Delegate_ID = tblDelegate.ID) INNER JOIN tblEvent ON tblBooking.Event_ID = tblEvent.ID;

I usually find it easiest to start building the query in design (grid) view and then switch to SQL.

I think I mentioned before, but I'll offer these bits again.
1. You might want to eliminate the underscore characters, they are a likely source of trouble down the line.
2. I would name your PK and FK fields the same. E.g. the PK for tblEvent should be EventID and then also use this in tblBooking. While it may seem confusing at first, it will actually help things out (such as when you try to do your JOINS).

hope that helps!

- g

 

[neileg]

I think I mentioned before, but I'll offer these bits again.
1. You might want to eliminate the underscore characters, they are a likely source of trouble down the line.

The underscore is the only punctuation that you can get away with. However, I don't think it helps readability and is just a wasted character.

2. I would name your PK and FK fields the same. E.g. the PK for tblEvent should be EventID and then also use this in tblBooking. While it may seem confusing at first, it will actually help things out (such as when you try to do your JOINS).

If you do use the same field name, the Access wizards will recognise the join even if it's not in the relationships diagram.