I need to get query like this:
| Name | COUNT |
|---|---|
| King | 3 |
| Queen | 1 |
| Single | 0 |
but when I use query
SELECT bedtype.bedTypeName ,COUNT(room_bed.bedTypeID) FROM room_bed
LEFT JOIN bedtype ON bedtype.bedTypeID = room_bed.bedTypeID
WHERE room_bed.roomID = 3
GROUP BY bedtype.bedTypeName
I got this:
| Name | COUNT |
|---|---|
| King | 3 |
| Queen | 1 |
How Can I get above result ?
Table 1 : bedType
| ID | bedTypeName |
|---|---|
| 1 | King |
| 2 | Queen |
| 3 | Single |
Table 2 : room_bed
| ID | bedTypeID | roomID |
|---|---|---|
| 1 | 1 | 3 |
| 2 | 1 | 3 |
| 3 | 1 | 3 |
| 4 | 2 | 3 |
You have a couple of problems. Firstly, you need to LEFT JOIN from bedType to room_bed to ensure that you get all bed types in the output result. Secondly, you cannot have a condition on the LEFT JOINed table in a WHERE clause; otherwise you convert that LEFT JOIN into an INNER JOIN (see the manual). Change your query to this:
SELECT b.bedTypeName AS Name, COUNT(r.id) AS `Count`
FROM bedType b
LEFT JOIN room_bed r ON b.ID = r.bedTypeID AND r.roomID = 3
GROUP BY b.bedTypeName
Output:
Name Count
King 3
Queen 1
Single 0
try invert the tables and use SUM checking for null
SELECT bedtype.bedTypeName , SUM(ifnull(room_bed.bedTypeID,0)) FROM bedtype
LEFT JOIN room_bed ON bedtype.bedTypeID = room_bed.bedTypeID
WHERE room_bed.roomID = 3
GROUP BY bedtype.bedTypeName