list all cars query...

[thompom]

hi,

the following query works out total booked days, per month, for a car booking query - but i would like the query to show all cars in the table 'courtcars'
so i can list all cars even if they dont have bookings.
at the moment it only shows cars which have a booking.

so far i have

SELECT CC.reg, month(cal.datefield), CC.carid, CBB.courtcarbookid,tran.trandesc, CC.model, COUNT(cal.datefield) AS NumDays 
FROM CourtCar AS CC 
LEFT JOIN 
(SELECT CB.bookst, CB.bookend, CB.carid, CB.courtcarbookid, CB.cusid 
FROM courtcarbook AS CB) 
AS CBB ON CC.carid = CBB.carid
LEFT JOIN cal ON cal.datefield >= CBB.Bookst 
AND cal.datefield <= CBB.Bookend 
LEFT JOIN tran ON CC.tranid = tran.tranid 
LEFT JOIN cus ON cus.cusid = CBB.cusid 
WHERE cal.datefield >= '20070101' 
AND cal.datefield <= '20071231' 
AND display = 0 
GROUP BY CC.carid, month(cal.datefield) 
ORDER BY CC.carid ASC, CC.makeid ASC, month(cal.datefield) ASC

 

[thompom]

think i got it

SELECT CC.reg, month(cbb.datefield), CC.carid, CBB.courtcarbookid,tran.trandesc, CC.model, COUNT(cbb.datefield) AS NumDays 
FROM CourtCar AS CC 
LEFT JOIN 
(SELECT CB.bookst, CB.bookend, CB.carid, CB.courtcarbookid, CB.cusid, cal.datefield 
FROM courtcarbook AS CB
LEFT JOIN cal ON cal.datefield >= CB.Bookst 
AND cal.datefield <= CB.Bookend 
WHERE cal.datefield >= '20070101' 
AND cal.datefield <= '20071231')
AS CBB ON CC.carid = CBB.carid
LEFT JOIN tran ON CC.tranid = tran.tranid 
LEFT JOIN cus ON cus.cusid = CBB.cusid 
AND display = 0 
GROUP BY CC.carid, month(cbb.datefield) 
ORDER BY CC.carid ASC, CC.makeid ASC, month(cbb.datefield) ASC