using multiple find_in_set in a single query

Question

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

i'm confused. how do you on if it is a yes or a no?

John Woo
– John Woo

2013-09-29 13:21:03 +00:00
Commented Sep 29, 2013 at 13:21 — John Woo
– John Woo, Commented Sep 29, 2013 at 13:21
what is also your desired result?

John Woo
– John Woo

2013-09-29 13:21:31 +00:00
Commented Sep 29, 2013 at 13:21 — John Woo
– John Woo, Commented Sep 29, 2013 at 13:21
@491243 edited the question adding the desired result

Lhen
– Lhen

2013-09-29 13:32:35 +00:00
Commented Sep 29, 2013 at 13:32 — Lhen
– Lhen, Commented Sep 29, 2013 at 13:32

John Woo · Accepted Answer · 2013-09-29 13:40:32Z

1

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

SQLFiddle Demo

answered Sep 29, 2013 at 13:40

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lhen Over a year ago

thank you. that works! now what if i have another column in the opinion table called points and I want to find the sum for each user. Only users in the yes column gets points

Lhen Over a year ago

it's a column for the opinion tables as well. every row has a corresponding point. Let's say, each of them is 2 points although they may vary. And only the users in the yes column gets to earn the point/s

Collectives™ on Stack Overflow

using multiple find_in_set in a single query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related