Two left joins from same table

[emozley]

I have three tables which I am attempting to join together however because I want to reference one of the tables twice in the statement I am not sure if it will work.

The Users table is basically a list of names - FirstName, Surname and UserID

The Meetings table has the details of all meetings - when they are, when they start and finish, who is organising them (OrganiserID) and so on.

The Attendees table has a UserID and a MeetingID.

The MeetingID field in Attendees ties in with the MeetingID field in the MeetingsTable and the UserID field in the Attendees ties in with the UserID field in the Users table. The OrganiserID is also meant to tie in with the UserID field in the Users table as well.

The idea is to execute a query that shows the name of the person attending the meeting and also the name of the person who is organising the meeting. Is this possible or would I have to re-organise my tables?

Thanks

SELECT Attendees.UserID, Attendees.MeetingID, Users.FirstName, Users.Surname, Meetings.Title, Meetings.RoomID, Meetings.Date, Meetings.StartTime FROM ((Attendees LEFT JOIN Users ON Attendees.UserID=Users.UserID) LEFT JOIN Users ON Attendees.OrganiserID=Users.UserID) LEFT JOIN Meetings ON Attendees.MeetingID=Meetings.MeetingID

 

[PHV]

Use aliasing:
SELECT A.UserID, U.FirstName As AttendeeFirstName, U.Surname As AttendeeSurname
, A.MeetingID, M.Title, M.RoomID, M.Date, M.StartTime
, A.OrganiserID, O.FirstName As OrganiserFirstName, O.Surname As OrganiserSurname
FROM ((Attendees As A
LEFT JOIN Users As U ON A.UserID=U.UserID)
LEFT JOIN Users As O ON A.OrganiserID=O.UserID)
LEFT JOIN Meetings As M ON A.MeetingID=M.MeetingID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[emozley]

Excellent thanks for this - I had to tweak it slightly but this is what I ended up with.

SELECT A.MeetingID, Users.FirstName, Users.Surname, Meetings.Title, Meetings.OrganiserID, Meetings.RoomID, Meetings.MeetingID, Meetings.Date, Meetings.StartTime, Meetings.EndTime, O.FirstName, O.Surname FROM ((Attendees AS A LEFT JOIN Users ON A.UserID=Users.UserID) LEFT JOIN Meetings ON A.MeetingID=Meetings.MeetingID) LEFT JOIN Users AS O ON Meetings.OrganiserID=O.UserID

WHERE Users.FirstName='ed' OR Users.Surname='ed' OR Users.FirstName & ' ' & Users.Surname='ed' OR O.FirstName='ed' OR O.Surname='ed' OR O.FirstName & ' ' & O.Surname='ed'

The final problem I have is that if I am the organiser of a meeting as well as being an attendee it will return multiple records. I tried putting the word 'DISTINCT' after SELECT but didn't have any luck.

I can copy and paste the results returned from Access if it would make it easier to explain.

cheers

Ed