Count records from two tables with foreign keys

Count records from two tables with foreign keys

Contents

Problem Description:

Suppose I have these MySql tables:

TABLE_PEOPLE

idname
1John
2Albert
3Joanna
4Mike
5Norton

TABLE_COLOR

idpeople_idcolors
11Green
21Red
33Yellow
43Blue
52Green
64Red
75Grey
83White
94Black
101Black

TABLE_FRUIT

idpeople_idfruits
11Lemon
22Apple
33Tangerine
45Orange
52Banana
61Apple
75Lemon
82Orange
93Watermelon
104Banana

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?

Solution – 1

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.

Solution – 2

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.

Rate this post
We use cookies in order to give you the best possible experience on our website. By continuing to use this site, you agree to our use of cookies.