I have two tables with the following sample data:
Table 1: `item_name`
| item_id | item_desc |
| 1 | apple |
| 2 | orange |
| 3 | banana |
| 4 | grape |
| 5 | mango |
Table 2: `user_items`
| user_id | item_id |
| 127 | 1 |
| 127 | 2 |
| 127 | 4 |
| 128 | 1 |
| 128 | 5 |
I'm trying to select a total of each item_id both user_id 127 and 128 have, along with the corresponding item_desc using the following query:
SELECT IFNULL(COUNT(ui.user_id), 0) AS total, in.item_desc
FROM user_items AS ui
RIGHT OUTER JOIN item_name AS in
ON ui.item_id = in.item_id
WHERE ui.user_id IN (127, 128)
GROUP BY ui.item_id
ORDER BY total DESC
The result of the above query is:
| total | item_desc |
| 2 | apple |
| 1 | orange |
| 1 | grape |
| 1 | mango |
but it didn't include item_id 3, banana, which I wanted to retrieve with RIGHT OUTER JOIN. I was hoping to get a result that looked like this:
| total | item_desc |
| 2 | apple |
| 1 | orange |
| 1 | grape |
| 1 | mango |
| 0 | banana |
Is there any way to modify the query to end up with the intended result above? Thank you for your time.
There was a little error in your query using count. This works.
select count(ui.item_id) as total, in.item_desc
from item_name `in`
left join user_items ui on ui.item_id = in.item_id
and ui.user_id in (127, 128)
group by
in.item_desc
order by total desc
Your WHERE
clause as it is is essentially removing all records from item_name
that don't relate to user_id
's 127 & 128.
In order to fix that, the easiest solution would be to LEFT JOIN
from the item_name
table to the user_items
table and apply the selection of user_id
's on the JOIN
.
SELECT COUNT(*), itn.item_desc
FROM item_name AS itn
LEFT OUTER JOIN user_items AS ui ON ui.item_id = itn.item_id
AND ui.user_id IN (127, 128)
GROUP BY
itn.item_desc
note that while this can be written as a RIGHT OUTER JOIN
, I find that type of joins to be counterintuïtive and would advise on only using them when absolutely necessary.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With