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

Table1 is the list they can choose from:

Table2 is they fill in their choices:

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

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

Could anyone help me with the query? Thanks in advance

CodePudding user response：

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

CodePudding user response：

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