Combining sql queries together

I am trying to simplify my current query code. Is there a simpler way of combining both queries into one?

Here is what I have tried so far. Although it seems really messy and clunky. This query requests all the clients

SELECT * FROM clients ORDER BY name DESC

I then have another request inside to find if the client has a booking

SELECT * FROM bookings WHERE client_id=$client_id AND done=0

Done represents if the job is completed, and there can only be 1 uncompleted job per client at a time.

Explanation:

The code needs to show all the clients and if the client has a booking and if the other database returns a result, the booking button won’t appear, if no results (i.e, all previous bookings for that client are 1) a book button appears.

Answer

I think you want:

select c.*, coalesce(b.done, 1) completed
from clients c
left join bookings b on b.client_id = c.client_id and b.done = 0

This attempts to find a uncompleted row in booking for each client. Column completed in the resultset contains 0 if the client has an uncompleted row, else 1.

Advertisement

Answer