Question 21884 in Mysql Question posted in
The official MySQL documentation can be found here.

Count records from two tables with foreign keys – Mysql

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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search