Search code examples
mysqlsqlgroup-bygroup-concat

Search within GROUP_CONCAT

I am executing this SQL from a big result of rows

SELECT userid, group_concat(locationid) FROM user_location group by userid having group_concat(locationid) = 10

userid  locationid
---------  ----------
894801  10,10,10,10,10,10,10,10,10,10,10,10
898356  10,10,11,10
900424  10,10,13,12,12,12,12
902123  10
904910  10,10
907922  10,10,10
912587  10,12,12
930319  10

Now, I want only those locationid rows where the value = 10 and no other value

Desired Output:

userid  locationid
---------  ----------
894801  10,10,10,10,10,10,10,10,10,10,10,10
902123  10
904910  10,10
907922  10,10,10
930319  10

I explored and found find_in_set() but no use here

Solution 
  • SELECT * FROM user_location WHERE userid not in (select userid from user_location where locationid<>10) 
group by userid having locationid=10;

    Try this.. It's working