Sum up columns between two tables in MySQL

Question

I have an application where students do three choices 1st, 2nd and 3rd from a list.

Table1 is the list they can choose from:

id	day
1	Monday
2	Thusday
3	Wednesday

Table2 is they fill in their choices:

id	first_choice	second_choice	third_choice
12345	1	3	2
23456	3	2	1
34567	2	1	3
45678	1	2	3

What I'm struggling with is that I want to count choices per day and priority to get a list like this:

id	first_choice	second_choice	third_choice
Monday	2	1	1
Thusday	1	2	1
Wednesday	1	1	2

SELECT a.day, count(b.first_choice), count(c.second_choice), count(d.third_choice) FROM table1 a LEFT JOIN table2 b ON a.id = b.first_choice LEFT JOIN table2 c ON a.id = c.second_choice LEFT JOIN table2 d ON a.id = d.third_choice GROUP BY a.day 

But, by doing so I end up with this

id	first_choice	second_choice	third_choice
Monday	2	2	2
Thusday	2	2	2
Wednesday	2	2	2

Could anyone help me with the query? Thanks in advance

Answer 1

You can use a single INNER JOIN and work it out with conditional IF statements:

SELECT  
    Table1.id, 
    Table1.day,
    COUNT(IF(Table2.first_choice=Table1.id, Table2.first_choice, NULL)) AS first_choice,
    COUNT(IF(Table2.second_choice=Table1.id, Table2.second_choice, NULL)) AS second_choice,
    COUNT(IF(Table2.third_choice=Table1.id, Table2.third_choice, NULL)) AS third_choice
FROM
    Table1
INNER JOIN 
    Table2
ON 
    Table1.id = Table2.first_choice 
OR 
    Table1.id = Table2.second_choice 
OR 
    Table1.id = Table2.third_choice 
GROUP BY 
    Table1.id,
    Table1.day

Refer to this fiddle: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=eea27fe9364c0fe4b96a846c9441f723

Answer 2

In those table structures, I normally use subquery instead of join.

SELECT
    a.day,
    (SELECT COUNT(*) FROM Table2 WHERE first_choice = a.id) AS first_choice,
    (SELECT COUNT(*) FROM Table2 WHERE second_choice = a.id) AS second_choice,
    (SELECT COUNT(*) FROM Table2 WHERE third_choice = a.id) AS third_choice
FROM Table1 a