Hello there,

I have tables like so (simplified):

-=Holiday=-
HolidayID
Name
Date

-=Booking=-
BookingID
HolidayID
ClientID

What I would like to do is create a query which returns a list of all the holidays with a field showing how many bookings have been created for each holiday. This almost works:

SELECT Count(1) AS CountOfBookingID, Bookings.HolidayID
FROM Bookings
GROUP BY Bookings.HolidayID;


...but it does not display holidays where there are no bookings.

Is it possible to create one which will show all holidays even if there are no bookings?

Thanks!:cool:

retro,


Select A.Name, Count(*)
From Holiday As A Inner Join Booking As B On
A.HolidayID = B.HolidayID
Group By A.Name


hth,
Wayne

Hello Wayne,

Thanks for your help!

That didn't quite solve it but it helped me get a step closer :)

I now have:
SELECT A.HolidayID, Count(*) AS [Count]
FROM Holidays AS A LEFT JOIN Bookings AS B ON A.HolidayID=B.HolidayLink
GROUP BY A.HolidayID;


This is pretty close, but instead of Count=0 where there are no matching bookings, I get Count=1. Hmmm...

Thanks again!

Hello Wayne,

Thanks for your help!

That didn't quite solve it but it helped me get a step closer :)

I now have:
SELECT A.HolidayID, Count(*) AS [Count]
FROM Holidays AS A LEFT JOIN Bookings AS B ON A.HolidayID=B.HolidayLink
GROUP BY A.HolidayID;


This is pretty close, but instead of Count=0 where there are no matching bookings, I get Count=1. Hmmm...

Thanks again!

OK, I think I got it working!

SELECT A.HolidayID, Count(B.BookingID) AS [Count]
FROM Holidays AS A LEFT JOIN Bookings AS B ON A.HolidayID=B.HolidayLink
GROUP BY A.HolidayID;


Cheers:)