How to include NULL values ​​in a query using Outer Join and Group By

Question

I have two tables with the following data examples:

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 am trying to select the total number of item_id and user_id elements 127 and 128, and the corresponding item_desc element 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

Result of the above query:

| total | item_desc |
| 2     | apple     |
| 1     | orange    |
| 1     | grape     |
| 1     | mango     |

but he did not include item_id 3, the banana I wanted with RIGHT OUTER JOIN. I was hoping to get a result that looks like this:

| total | item_desc |
| 2     | apple     |
| 1     | orange    |
| 1     | grape     |
| 1     | mango     |
| 0     | banana    |

Is there a way to modify the query ultimately with the above result? Thank you for your time.

Answer 1

There was a small error in your request using count. It 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

Answer 2

WHERE, , item_name, user_id 127 128.

, LEFT JOIN item_name user_items user_id 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

, , RIGHT OUTER JOIN, , , .