Records on Join Table

[barrybazouk]

I Have the following query:

SELECT Count(tblBookings.BookingID) AS CountOfBookingID, tblSessions.SessionID, tblSessions.SessionDate
FROM tblCourses INNER JOIN ((tblVenues INNER JOIN tblSessions ON tblVenues.VenueID = tblSessions.VenueID) INNER JOIN tblBookings ON tblSessions.SessionID = tblBookings.SessionID) ON tblCourses.CourseID = tblSessions.CourseID
WHERE (((tblBookings.Status)<>4) AND (([tblCourses].[CourseID])=[@CourseID]) AND ((tblSessions.SessionID)=[@SessionID]))
GROUP BY tblSessions.SessionID, tblSessions.SessionDate
HAVING (((Count(tblBookings.BookingID))<Max([tblVenues].[Capacity])));

This takes as input @CourseID and @SessionID. It checks to see if the total number of bookings made by delegates is less than the Venues capacity.

There is only one problem with this, say if there is no bookings for a Session then it will not bring back any results. Even though you should be able to book Sessions for that course.

Your help is much appreciated.

 

[neileg]

An inner join requires matching data on both sides of the join. What you need is an outer join. This returns all data from one side and any data that matches from the other side. These are left and right joins, the only difference being which way round the tables are. You can change the join in the query grid by right clicking the join line and selecting whichever relationship you need.

 

[barrybazouk]

Ok, so what will the new SQL be.

 

[Rabbie]

Ok, so what will the new SQL be.

if you do as Neil suggested in the query grid then you can see the new SQL by switching to SQL view

 

[barrybazouk]

From the SQL View I went to Design View and right clicked the relationship, selected join properties and then number 2: 'Include all records from tblSessions' and those records from 'tblBookings' where the table field are matched, this changed the inner join to left join but still no record comes up.

 

[neileg]

You've got two inner joins in your query.

 

[barrybazouk]

Neil, your not making any sense to me at all. If you know what to do then adjust the query and send it to me.

 

[Rabbie]

Looks more like three to me!

SELECT Count(tblBookings.BookingID) AS CountOfBookingID, tblSessions.SessionID, tblSessions.SessionDate
FROM tblCourses INNER JOIN ((tblVenues INNER JOIN tblSessions ON tblVenues.VenueID = tblSessions.VenueID) INNER JOIN tblBookings ON tblSessions.SessionID = tblBookings.SessionID) ON tblCourses.CourseID = tblSessions.CourseID
WHERE (((tblBookings.Status)<>4) AND (([tblCourses].[CourseID])=[@CourseID]) AND ((tblSessions.SessionID)=[@SessionID]))
GROUP BY tblSessions.SessionID, tblSessions.SessionDate
HAVING (((Count(tblBookings.BookingID))<Max([tblVenues].[Capacity])));

 

[neileg]

FROM tblCourses INNER JOIN ((tblVenues INNER JOIN tblSessions ON tblVenues.VenueID = tblSessions.VenueID) [COLOR="Red"]LEFT[/COLOR] JOIN tblBookings ON tblSessions.SessionID = tblBookings.SessionID) ON tblCourses.CourseID = tblSessions.CourseID

I would have thought this was the change needed, but you haven't given any information on your structure so I'm guessing. If you post a stripped down version of your db that would help.