CODEWITHSUNDEEP
mysql
LargeTuna
LargeTuna

Reputation: 2824

MySQL filter results from a SubSelect using AND or HAVING

I have a query that looks basically like this: 

Select t.id, 
       (
       Select GROUP_CONCAT(CONCAT_WS(' ', td.id) SEPARATOR ',') as list
       From Table t2
       ) as id_list // this will add a comma delimited list
From Table t1
Where t1.status IS NULL
And id_list IN (3)

The query result is like this...

id|id_list
-------------------- 
1 |1
9 |1,3,12,10,15

This does not work as MySQL will not allow me to filter by id_list as an And conditional...

I have also tried this in place of the And conditional, but it does not work also...

Having id_list IN (3)

How can I filter these results based upon the id_list matching some parameter which I set, in this case 3.

I just want to return record id 9, not 1.

Thanks

Upvotes: 0

Views: 68

Answers (2)

spencer7593
spencer7593

Reputation: 108430

To check for a value '3' in a comma separated list e.g. '2,3,5' we can use MySQL FIND_IN_SET function.

As a demonstration:

SELECT FIND_IN_SET('3','2,3,5') returns 2

SELECT FIND_IN_SET('3','7,11') returns 0

So for the query in the question, given id_list is a comma separated list, we could do

 HAVING FIND_IN_SET('3',id_list)

or equivalently

 HAVING FIND_IN_SET('3',id_list) > 0

Note that the IN comparison operator does not work like the FIND_IN_SET function.

The IN is equivalent to equality comparison of individual values. For example, 

SELECT 3 IN (2,3,5)

is equivalent to 

SELECT 3 = 2 OR 3 = 3 OR 3 = 5

As another example:

SELECT 3 IN ('2,3,5')

is equivalent to

SELECT 3 = '2,3,5'

Upvotes: 1

Alex
Alex

Reputation: 17289

Just a guess:

SELECT t.id, 
       (
       SELECT GROUP_CONCAT(CONCAT_WS(' ', td.id) SEPARATOR ',') as list
       FROM Table t2
       ) as id_list 
FROM Table t1
WHERE t1.status IS NULL
AND FIND_IN_SET(3, id_list) > 0

Upvotes: 0

Related Questions