CODEWITHSUNDEEP
mysqlsqljoin
Rikard
Rikard

Reputation: 7805

Join tables keeping empty results

I have two tables that I need to cross and return as many results as the ids of one of them.

The first is a table of roles/tasks:

id | rolename
---+---------
1  | check_in
2  | cleaning
3  | taxi
4  | guide
5  | car_rental
6  | meals
7  | house_owner
20 | custom

and another table that has the columns:

id | client_booking_id | staff_role_id | confirmed | staff_cost

I need a query that always gives me as many results as nr of columns in the first table. Because for each unique client_booking_id there will be only one (if any) of those tasks/roles.

So if I do:

SELECT sr.role_name, sr.id, ss.staff_cost, ss.confirmed 
    FROM staff_role AS sr
    LEFT JOIN staff_schedule AS ss ON sr.id=ss.staff_role_id

I get a result with the nr of rows I want. Now I need to match it with a specific client_booking_id so I did

SELECT sr.role_name, sr.id, ss.staff_cost, ss.confirmed 
    FROM staff_role AS sr
    LEFT JOIN staff_schedule AS ss ON sr.id=ss.staff_role_id
    WHERE ss.client_booking_id=1551 // <-- this is the new line

And this gives me only 2 results because in the second table I have only booked 2 tasks to a id.

But I need a result with all tasks even those that do not match, with NULL values. How can I do this?

Upvotes: 0

Views: 75

Answers (1)

potashin
potashin

Reputation: 44581

With your query (without where clause) you get rows with null and non-null values for client_booking_id. You want to match specific client_booking_id and at the same time leave all records with null values, so you add additional condition with specific client_booking_id to left join.

Moving condition to left join:

select sr.role_name
     , sr.id
     , ss.staff_cost
     , ss.confirmed 
from staff_role sr
left join staff_schedule ss on sr.id = ss.staff_role_id
                           and ss.client_booking_id = 1551

Upvotes: 1

Related Questions