Sergey Ovchinnik Sergey Ovchinnik - 2 months ago 6
MySQL Question

How many times each value from a list appears in a column in another table. MYSQL

I have a table of id values and need to count how many times each of those ids appears in a column in another table.

I have figured out how to do it only for values that appear at least once:

SELECT one.id, COUNT(*) FROM table1 one, table2 two WHERE one.id = two.id GROUP BY one.id;


but can't figure out how to also include ids that appear in first but don't appear in second table at all.

Example:

table1: table2:

+-----+ +-----+
| id | | id |
+-----+ +-----+
| 11 | | 11 |
| 12 | | 12 |
| 13 | | 14 |
| 14 | | 11 |
+-----+ | 11 |
| 12 |
+-----+


The result would be:

+-----+----------+
| id | count(*) |
+-----+----------+
| 11 | 3 |
| 12 | 2 |
| 14 | 1 |
+-----+----------+


I'm trying to make it also include line
| 13 | 0 |

SMA SMA
Answer

You are doing an implicit inner join which is discouraged. Instead what you need is a left join, something like:

SELECT one.id, COUNT(two.id) 
FROM table1 one LEFT JOIN table2 two 
ON one.id = two.id 
GROUP BY one.id;