CODEWITHSUNDEEP
mysqlsqljoinleft-join
JoshuaJeanThree
JoshuaJeanThree

Reputation: 1392

MySQL - Get left table elements although 'where' condition is not compatible

I have two tables that matches a staff and his/her books. I want to get all staff in result although s/he has no books (count is 0). However, the code I wrote below returns only the staff that has at least one book compatible with where condition. How can I get all staff and the staff that has no book as having count 0?

SELECT staff.name AS sname, Count(book.book_id) AS bcount 
FROM db_staff AS staff 
LEFT JOIN db_book AS book ON book.staff_id = staff.staff_id 
WHERE (book.status_id = 1 OR book.status_id = 3) 
GROUP BY staff.staff_id

Upvotes: 0

Views: 48

Answers (2)

Rudra Sisodia
Rudra Sisodia

Reputation: 133

Here you can use left outer join which select all the rows from left table regardless of its match to the right table in case it does not match it return null where in our case need zero so here is the code . isnull method convert null value to the value you mention. SELECT staff.name AS sname, Isnull(Count(book.staff_id),0) AS bcount FROM db_staff AS staff LEFT OUTER JOIN db_book AS book ON book.staff_id = staff.staff_id GROUP BY staff.staff_id

Happie coding

Upvotes: 0

shree.pat18
shree.pat18

Reputation: 21757

Move the filter condition from Where to And clause, like so:

SELECT staff.name AS sname, Count(book.book_id) AS bcount 
FROM db_staff AS staff 
LEFT JOIN db_book AS book ON book.staff_id = staff.staff_id 
AND (book.status_id = 1 OR book.status_id = 3) 
GROUP BY staff.staff_id

Explanation: When there is a where clause, the join results are filtered, and rows which do not satisfy the condition are filtered out. When the same condition is made part of the on clause, it is treated as one of the join conditions. Hence, in a left join, the left side rows will be returned even if the right side does not have a row satisfying all conditions in the join.

http://sqlfiddle.com/#!2/75344/3

Upvotes: 1

Related Questions