Count records from two tables with foreign keys-CodePudding

Suppose I have these MySql tables:

TABLE_PEOPLE

id	name
1	John
2	Albert
3	Joanna
4	Mike
5	Norton

TABLE_COLOR

id	people_id	colors
1	1	Green
2	1	Red
3	3	Yellow
4	3	Blue
5	2	Green
6	4	Red
7	5	Grey
8	3	White
9	4	Black
10	1	Black

TABLE_FRUIT

id	people_id	fruits
1	1	Lemon
2	2	Apple
3	3	Tangerine
4	5	Orange
5	2	Banana
6	1	Apple
7	5	Lemon
8	2	Orange
9	3	Watermelon
10	4	Banana

What I'd like to have is a query with numbers of occurrences of colors and fruits for each person:

RESULTS | name| count_colors | count_fruits | |:----:|:----:|:-------:| |John|3|2| |Albert|1|3| |Joanna|3|2| |Mike|2|1| |Norton|1|1|

I'm trying to use this query, but it returns some inconsistent numbers:

SELECT
TABLE_PEOPLE.name AS name,
COUNT(TABLE_COLOR.people_id) AS count_colors,
COUNT(TABLE_FRUIT.people_id) AS count_fruits
FROM TABLE_PEOPLE
LEFT JOIN TABLE_COLOR ON TABLE_COLOR.people_id = TABLE_PEOPLE.id
LEFT JOIN TABLE_FRUIT ON TABLE_FRUIT.people_id = TABLE_PEOPLE.id
GROUP BY TABLE_PEOPLE.id
ORDER BY TABLE_PEOPLE.id

Any idea?

CodePudding user response：

The issue with your current query is that you are using the COUNT function to count the number of occurrences of people_id in TABLE_COLOR and TABLE_FRUIT. However, this will not give you the correct result because the COUNT function will only return the number of non-NULL values, and since you are using LEFT JOIN, all of the people_id values will be non-NULL, so COUNT will always return the same value for each person.

To fix this issue, you can use a subquery in your SELECT clause to count the number of occurrences of colors and fruits for each person:

SELECT
  TABLE_PEOPLE.name AS name,
  (SELECT COUNT(*) FROM TABLE_COLOR WHERE TABLE_COLOR.people_id = TABLE_PEOPLE.id) AS count_colors,
  (SELECT COUNT(*) FROM TABLE_FRUIT WHERE TABLE_FRUIT.people_id = TABLE_PEOPLE.id) AS count_fruits
FROM TABLE_PEOPLE
ORDER BY TABLE_PEOPLE.id

This will return the correct number of occurrences of colors and fruits for each person.

CodePudding user response：

You should join to subqueries which find the various counts:

SELECT
    p.name,
    COALESCE(c.cnt, 0) AS count_colors,
    COALESCE(f.cnt, 0) AS count_fruits
FROM TABLE_PEOPLE p
LEFT JOIN
(
    SELECT people_id, COUNT(*) AS cnt
    FROM TABLE_COLOR
    GROUP BY people_id
) c
    ON c.people_id = p.id
LEFT JOIN
(
    SELECT people_id, COUNT(*) AS cnt
    FROM TABLE_FRUIT
    GROUP BY people_id
) f
    ON f.people_id = p.id
ORDER BY
    p.id;

Your exact problem is happening because of the double join to the colors and fruits table, which results in multiplication of records.