Querying on count value having ()

Question

I'm having difficulty with a query which displays records according to their fill rate.

For instance, a vacancy can have no bookings or some bookings. If a vacancy has bookings, they can be in the form of 'active [1]', 'pending [0]'. The query I have written so far works if the vacancy has booking records but I can't get it to work if it doesn't have booking records.

My query (which works) for vacancies with a booking is as follows:-

SELECT v.*, j.job_category_name, bu.business_unit_name 
FROM vacancy v 
INNER JOIN job_category j ON j.job_category_id = v.job_category_id 
INNER JOIN business_unit bu ON bu.business_unit_id = v.business_unit_id 
INNER JOIN booking b ON b.vacancy_id = v.vacancy_id 
INNER JOIN booking_status bs ON bs.id = b.booking_status_id 
WHERE 
    v.vacancy_status <> 'revoked' AND 
    v.vacancy_reference <> 'auto-generated booking' AND 
    v.business_unit_id IN (series of primary keys) AND 
(bs.booking_status_type_id = 1 OR bs.booking_status_type_id = 2) 
GROUP BY v.vacancy_id 
HAVING v.vacancy_limit > count(b.booking_id)
ORDER BY v.vacancy_id DESC 

I thought by changing the join of b and bs to LEFT JOIN would have worked, but it hasn't.

Any ideas?

Answer 1

Without a copy of your schema to work from, it's difficult to tell exactly, but when you changed booking and bookingstatus to LEFT JOINs, did you also modify your WHERE clause so that it read something like:

WHERE 
  v.vacancy_status <> 'revoked' AND 
  v.vacancy_reference <> 'auto-generated booking' AND 
  v.business_unit_id IN (series of primary keys) AND 
  (ISNULL(bs.booking_status_type_id, 1) = 1 OR ISNULL(bs.booking_status_type_id, 2) = 2) 

i.e. Ensured that the full WHERE clause would be satisfied, thus not stripping out the records where all the values for columns from booking and bookingstatus were NULL?

Answer 2

Try LEFT OUTER JOIN for the tables for which the joins may return 0 matches. For eg:- in your case have LEFT OUTER JOIN for b and bs and check