group_concat result not work in IN condition

4
votes

Is there any possible way to put result of group_concat in IN condition of SQL query.

Here in network master table i have comma separated fields in industryId column. Like,

userId     industryId
123        3831
123        2832,3832
123        3833

Example:

select group_concat(industryId order by cId SEPARATOR ',') from network_master where userId = 123

and it gives me this type of output 3831,2832,3832,3833 I got this type of output using upper query,

userId      industryId
123         3831,2832,3832,3833

and now i done this things,

select * from industry_master where industryId in (select group_concat(industryId order by cId SEPARATOR ',') from network_master where userId =123 group by userId);

In this query result, I got details output of industryId=3831 only. I did not get other Ids output.

I need all the industryId output in this query. How to i achieve this things in mysql.

Any help would be appreciated.

2
mysql

2 Answers

3
votes

I have tried above case but not working on my side. I just edited above answer and removed > 0 then I can see your expected output.

Can you try below code?

select * from industry_master where find_in_set(industryId,  (select group_concat(industryId order by cId SEPARATOR ',') 
from network_master where userId = 123 group by userId));
2
votes

you don't need group_concat and IN clause you can use a join 

  select i.* 
  from industry_master i
  INNER JOIN network_master  n on  i.industryId = n.industryId 
  AND n.userId =123

group_concat return a string but you need values so using the string in IN clause don't work correctly.

or if can work only an a string you could trying using field_in_set > 0

select * from industry_master 
where find_in_set(industryId,  select group_concat(industryId order by cId SEPARATOR ',') 
      from network_master where userId =123 group by userId)   ;

or