Join only works when data is present

[emozley]

Hi,

I am trying to join some tables with the following statement:

SELECT 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

The idea is to produce a recordset that shows the name of a person attending a meeting, details of the meeting and the organiser of the meeting. I am using aliasing because it looks up the name of the attendee and the name of the organiser from the same table (Users).

It works to an extent however if a meeting exists but noone is attending it it does omits the record. I have come across something similar before but I thought using a left join would fix it. I tried opening the query in Design View in Access and double clicking the arrows showing the links between the tables and selecting the middle option on all of them but that does not appear to have fixed the problem.

Any idea where I might be going wrong? If you need any more info about the table structure/contents please let me know.

Thanks very much

Ed

 

[hneal98]

That would be expected since everything in your query is based on the first table, Attendees. I am assuming that if no one attends the meeting, they would not appear in the Attendees table.

If no one attends, what do you want to have happen?

 

[emozley]

Ideally I'm trying to produce a list/recordset of all meetings something like below (I have left out some fields)

MeetingID | Attendee | Date | Organiser

1 Ed 12/12/2005 Ed
1 Pete 12/12/2005 Ed
1 Harry 12/12/2005 Ed
2 Emily 01/01/2006 Emily
2 Ed 01/01/2006 Emily
3 - 01/04/2006 Ed

That is to say - even though I (Ed) have organised a meeting on 1st April 2006 noone is listed in my Attendees table as attending a meeting with MeetingID value of 3. However I would still want to know that that meeting existed.

Do I just need to re-organise the order of the statement?

Thanks

 

[hneal98]

Yes, however; I don't know what fields you have in all your tables. From the looks of it, I would suggest putting the Meeting 1st in your query, since it is all meetings that you want to see. Then everything else would be a left join.

 

[lespaul]

you may also want to read Understanding SQL Joins, it will help you to understand how to acheive what hneal98 suggested (the left joins).

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[emozley]

Thanks for this - I have now gone right back to basics with the statement:

SELECT MeetingID, O.FirstName
FROM Meetings LEFT JOIN Users AS O ON Meetings.OrganiserID=O.UserID;

This gives me the MeetingID and FirstName (of the organiser).

If I want to add the firstname of someone who is attending let's call this A.FirstName this should be easy but I keep on getting syntax errors - my latest effort is:

SELECT MeetingID, O.FirstName, A.FirstName
FROM (Meetings LEFT JOIN Users AS O ON Meetings.OrganiserID=O.UserID) LEFT JOIN Users AS A ON Attendees.MeetingID=Meetings.MeetingID

The problem I'm running into is that my table 'Meetings' has a value for orgnaniserID which corresponds with UserID in my table called 'Users'. However for keeping track of who is actually attending a meeting I have a table called Attendees which has columns UserID and MeetingID.

UserID is meant to tie in with UserID in Users and MeetingID is meant to tie in with the MeetingID value in my 'Meetings' table.

 

[lespaul]

well, since there will always be an organizer for the meeting, right? and you didn't use the A alias in your second join statement:

SELECT MeetingID, O.FirstName, A.FirstName
FROM (Meetings INNER JOIN Users AS O ON Meetings.OrganiserID=O.UserID) LEFT JOIN Users AS A ON A.MeetingID=Meetings.MeetingID



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[emozley]

Success! In the end I went for:

SELECT Meetings.MeetingID, Attendees.UserID, O.FirstName, A.FirstName
FROM ((Meetings LEFT JOIN Attendees ON Attendees.MeetingID=Meetings.MeetingID)
LEFT JOIN Users AS O ON Meetings.OrganiserID=O.UserID)
LEFT JOIN Users AS A ON Attendees.UserID=A.UserID;

Thanks very much for your help.

Ed