CODEWITHSUNDEEP
mysql
Lhen
Lhen

Reputation: 181

using multiple find_in_set in a single query

I have 2 tables:

one is users with "userid" column, the other one is opinion with "yes" and "no" columns. The "yes" and "no" columns both contain comma separated values.

What I wanted is to count the number of times a "userid" appears in the "yes" column as well as the number of times it appears in the "no" column.

What I have right now is this

SELECT userid, COUNT(yes), COUNT(no)
FROM users LEFT JOIN opinion ON (FIND_IN_SET( userid, yes) > 0)
GROUP BY userid

The above works as it counts the # of times each user appears in the "yes" column but incorrectly on the "no" column. echoing "no" results to the same result as echoing "yes"

Sample data on "users"

1
2
3

Sample data on "yes"

1,2
1,3
2,3
1

Sample data on "no"

1,2
1,2
1,3

desired result is with data above:

users     yes    no
1         3      3
2         2      2
3         2      1

Thanks in advance for any help

Upvotes: 0

Views: 2482

Answers (1)

John Woo
John Woo

Reputation: 263843

Your query needs only a little tweak. Add also a condition that joins userID with column NO on the other table. During counting of values, you also need to use FIND_IN_SET() inside the aggregate function MAX().

SELECT  a.userID,
        SUM(FIND_IN_SET(a.userID, b.yes) > 0) TotalYes,
        SUM(FIND_IN_SET(a.userID, b.no) > 0) TotalNo
FROM    users a
        LEFT JOIN opinion b
            ON FIND_IN_SET(a.userID, b.yes) > 0 OR
                FIND_IN_SET(a.userID, b.no) > 0 
GROUP   BY a.userID
ORDER   BY a.userID

Upvotes: 1

Related Questions