mysqlsubquerymysql-dependent-subquery

MySql - SubQuery is not working properly


BubQuery always return the empty result if the query is correct.

1st Query:

SELECT * FROM `user` WHERE user_age IN(1,22,34);

Result:

enter image description here

2nd Query:

SELECT GROUP_CONCAT(user_age_list) AS user_age FROM `user_detail` WHERE id='1';

Result: enter image description here

I am try:

SELECT * FROM `user` WHERE user_age IN(SELECT GROUP_CONCAT(user_age_list) AS user_age FROM `user_detail` WHERE id='1');

Sqlfiddle: http://sqlfiddle.com./#!9/d6515f/3 //This is a sample table.

Above the query is always return the empty rows. But each of query return the result if its run single. Really I don't know where is the error. Please update the answer or suggest me.


Solution

  • Avoid Use of GROUP_CONCAT

    SELECT * 
    FROM `user` 
    WHERE user_age IN(SELECT user_age_list FROM `user_detail` WHERE id='1');
    

    UPDATED

    SELECT * 
    FROM `user` u 
    WHERE EXISTS (SELECT 1 FROM `user_detail` ud  WHERE id='1' AND ud.user_age_list = u.user_age)