Returning wrong values when using count () and join in mysql

Question

I have two tables: Table A and Table B:

Table A

+--------+----------+------------+
| cat_id | cat_name | is_deleted |
+--------+----------+------------+
| 1      | name 1   | 0          |
+--------+----------+------------+
| 2      | name 2   | 0          |
+--------+----------+------------+
| 3      | name 3   | 1          |
+--------+----------+------------+

Table B

+--------------+----------+--------+------------+
| event_cat_id | event_id | cat_id | is_deleted |
+--------------+----------+--------+------------+
| 1            | 13       | 2      | 0          |
+--------------+----------+--------+------------+
| 2            | 23       | 2      | 1          |
+--------------+----------+--------+------------+
| 3            | 3        | 2      | 0          |
+--------------+----------+--------+------------+

I need to retrieve all records from Table A and get the amount of event_id for each cat_id from Table B so that I would have the following result for the above values:

"categories": [
    {
        "cat_name": "Name 1",
        "is_deleted": "0",
        "total_items": "0"
    },
    {
        "cat_name": "Name 2",
        "is_deleted": "0",
        "total_items": "2"
    }
]

What I tried so far without success was as follows:

SELECT TABLE_A.cat_name, TABLE_A.is_deleted, 
COUNT(TABLE_B.cat_id) AS total_items 
FROM TABLE_A LEFT JOIN TABLE_B ON (TABLE_A.cat_id = TABLE_B.cat_id) 
WHERE TABLE_A.is_deleted = 0 AND TABLE_B.is_deleted = 0 
GROUP BY TABLE_A.cat_name

This query returns wrong values for total_items. Why?

Possible duplicate of [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/q/4707673/3404097) — philipxy, Sep 07 '19 at 20:22
This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. — philipxy, Sep 07 '19 at 20:28
Please in code questions give a [mre]--cut & paste & runnable code; example input (as tabular initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) — philipxy, Sep 07 '19 at 20:28
Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right [sic] table column to be not NULL after an OUTER JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. — philipxy, Sep 07 '19 at 20:28

score 1 · Accepted Answer · answered Sep 07 '19 at 19:51

Set the condition TABLE_B.is_deleted = 0 in the ON clause instead of WHERE:

SELECT TABLE_A.cat_name, TABLE_A.is_deleted, 
COUNT(TABLE_B.cat_id) AS total_items 
FROM TABLE_A LEFT JOIN TABLE_B 
ON (TABLE_A.cat_id = TABLE_B.cat_id) AND TABLE_B.is_deleted = 0 
WHERE TABLE_A.is_deleted = 0 
GROUP BY TABLE_A.cat_name

If the condition is in the WHERE clause then the LEFT join is turned to an INNER join, removing all the unmatched rows from TABLE_B.
See the demo.
Results:

| cat_name | is_deleted | total_items |
| -------- | ---------- | ----------- |
| name 1   | 0          | 0           |
| name 2   | 0          | 2           |

score 1 · Answer 2 · answered Sep 07 '19 at 20:19

forpas already answered it but in case it helps see his logic in a different way

SELECT TABLE_A.cat_name, TABLE_A.is_deleted, 
COUNT(TABLE_B.cat_id) AS total_items 
FROM TABLE_A LEFT JOIN TABLE_B 
ON (TABLE_A.cat_id = TABLE_B.cat_id) 
WHERE TABLE_A.is_deleted = 0 AND **(TABLE_B.is_deleted = 0  OR TABLE_B.is_deleted IS NULL)**
GROUP BY TABLE_A.cat_name;

Returning wrong values when using count () and join in mysql

2 Answers2