Sql - struggling with count/having query based on several joins

Question

Hi,

I'm struggling to get my query working. Basically, what it should do is only return vacancies that haven't been filled. For example, a vacancy can have a pre-determined limit of 5. To fill this, it will need to have a mixture of 5 active or pending bookings or simply 5 active or 5 pending bookings.

A pending booking is changed to active when a client accepts it.

It may help by providing a basic schema of my set up:-

**tbl.vacancies:**
vacancy_id
job_category_id
business_unit_id
start_date
publication_date
end_date
limit - how many bookings it will accept

**tbl.bookings**
booking_id
candidate_id
vacancy_id
booking_status_id
user_id
date

**btl.booking_status**
id
user_id
booking_status_type_id
date

**tbl.booking_status_type**
id (1,2,3,4)
name (pending, active, rejected, revoked)

My query works fine when displaying all vacancies as I create an inner join between the job_category, business_unit, candidate, booking_status and booking_status_type tables.

But I have a requirement where I need to show filtered data based on if a vacancy has been fulfilled i.e. It has less active/pending bookings than the vacancies limit or it has been fully booked.

The query I'm currently using is:

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

The problem occurs when I revoke a booking (booking_status_type_id 4). It seems to mess up the results or not pick up any.

A little scenario of a status change on a booking could be:-

1. User creates booking (Pending state)
2. Client accepts (Active state)
3. Client then decides to revoke (Revoke state)

This will result in entries like so:-

Vacancy ID = 100;
Booking ID = 10;
Booking Status entries:-

Booking_id    Booking_status_type_id
---------------------------------------
10            1
10            2
10            4

So in theory this booking shouldn't display in the results as the position has been removed and become available again.

This will result in 3 entries in the booking_status_type table for the status changes on the booking.

I'm unsure as to why my query isn't working or if it is constructed correctly at all. As a basic requirement, when the user opts to select if a booking has been unfilled it should only show vacancies which the sum of the bookings aren't greater than the limit for the booking.

Likewise, when selecting to filter on filled vacancies, it should only show vacancies where the limit has been reached.

Edit #1

Okay I've tried implementing the suggested solution but cannot get it work:

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  
(SELECT booking_id AS bk_id, max(booking_status_type_id) AS bStatus
FROM booking_status
GROUP BY bk_id
HAVING bStatus < 3) as filter ON filter.bk_id = b.booking_id 
WHERE  
    v.status <> 'revoked' AND  
    v.reference <> 'auto-generated booking' AND  
    v.business_unit_id IN (1, 2) 
GROUP BY v.vacancy_id  
HAVING v.limit > count(b.booking_id) 
ORDER BY v.vacancy_id DESC

I get the following error:

1064 - You have an error in your SQL syntax. Check the manual that

corresponds to your MySQL server version for the right syntax to use near 'SELECT booking_id AS bk_id , max( booking_status_type_id ) AS b

The version of MySQL is 4.0

Answer 1

the problem is you have ignored the fact that the booking status could have multiple entires for the same booking id, and so you will need to first filter the bookingids that are valid like below

select booking_id as bk_id, max(Booking_status_type_id) status
from booking_status
group by booking_id
having status < 3

this query will return you only the booking ids which are pending or active, then you can filter the vacancies based on this further applying all the joins you have applied in the query you mentioned. i have roughly modified your query to do this, check if it works

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 
(select booking_id as bk_id, max(Booking_status_type_id) status
from booking_status
group by booking_id
having status < 3) as filter ON filter.bk_id = b.booking_id
WHERE 
    v.vacancy_status <> 'revoked' AND 
    v.vacancy_reference <> 'auto-generated booking' AND 
    v.business_unit_id IN (series of primary keys)
GROUP BY v.vacancy_id 
HAVING v.vacancy_limit > count(b.booking_id)
ORDER BY v.vacancy_id DESC

Answer 2

You are counting the bookings that have status of 1 or 2 but I think you should be counting the bookings that don't have status of 3 or 4 instead -- i.e. eliminate the rejected or revoked bookings.

Actually, I see now that you are only selecting one booking status using the join from the bookings table. I suspect the data then. Are you sure that the booking status referred to by the id in *booking_status_id* is the latest one?

Having poked around with this, I would check the following:

1. Are there multiple booking statuses with the same id?
2. Is the linked booking status the latest one?
3. Are you sure you're joining on the status id, not the booking id?

Other than that, I can't see it.