switch case from subquery sql

Assume that I have 6 tables: Users, Customers, Providers (each user must be or customer or provider), Requests, Offers, Booking.

Each customer can publish new request, and each provider can make an offer for any request, if the customer want he can make booking for one of the offer (each request may have at most one booking).

I want to crate query that shows for each request its status, if there is no offer for this request, show ‘Waiting for offer’, if there at least one request for this request but there is no booking show ‘Waiting for booking’, and if there booking for this request show ‘closed’.

At first I tried:

SELECT 
    R.RequestID, O.OfferID, B.OfferID,
    CASE 
       WHEN O.OfferID IS NULL THEN 'Waiting for offer'
       WHEN O.OfferID IS NOT NULL AND B.OfferID IS NULL THEN 'Waiting for booking'
       WHEN B.OfferID IS NOT NULL THEN 'Closed'
    END AS Status
FROM
    Requests AS R 
LEFT JOIN 
    Offers AS O ON O.RequestID = R.RequestID 
LEFT JOIN 
    Bookings AS B ON B.OfferID = O.OfferID

Results:

this the results of this query, I added the columns offerID and BookingID To make it more understandable

But of course that’s obviously not working, because it returns the booking and all the offers for each request, and for each offer its show ‘Waiting for booking’ or ‘Closed’.

For this example the output for requestID ‘300’ Should appear just once, with status ‘closed’.

I know that I need to use in subquery for each request and check if there any offer and if so check if there booking for this request, but I don’t find the way to check this checks on the subquery, is there way or maybe switch case or if else for this?

Answer

Seems like you don’t want to see the booking data if closed, and in that case you want to see only the closed status. I think you need two queries with a union to get the results you want. Maybe something like this. Hope it helps.

SELECT 
    R.RequestID, O.OfferID, B.OfferID,
    CASE 
       WHEN O.OfferID IS NULL THEN 'Waiting for offer'
       WHEN O.OfferID IS NOT NULL AND B.OfferID IS NULL THEN 'Waiting for booking'
       WHEN B.OfferID IS NOT NULL THEN 'Closed'
    END AS Status
FROM
    Requests AS R 
LEFT JOIN 
    Offers AS O ON O.RequestID = R.RequestID 
LEFT JOIN 
    Bookings AS B ON B.OfferID = O.OfferID
where B.OfferID IS NOT NULL -- where request is closed only
UNION 
SELECT 
    R.RequestID, O.OfferID, B.OfferID,
    CASE 
       WHEN O.OfferID IS NULL THEN 'Waiting for offer'
       WHEN O.OfferID IS NOT NULL AND B.OfferID IS NULL THEN 'Waiting for booking'
       WHEN B.OfferID IS NOT NULL THEN 'Closed'
    END AS Status
FROM
    Requests AS R 
LEFT JOIN 
    Offers AS O ON O.RequestID = R.RequestID 
LEFT JOIN 
    Bookings AS B ON B.OfferID = O.OfferID
where R.requestid not in (

select requestid from requests R
LEFT JOIN 
    Offers AS O ON O.RequestID = R.RequestID 
LEFT JOIN 
    Bookings AS B ON B.OfferID = O.OfferID
where B.OfferID IS NULL

)  -- where request is not closed only