MySql - SubQuery is not working properly

Question

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

1st Query:

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

Result:

2nd Query:

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

Result:

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.

See normalization. Normalize your schema. If the problem persists, then see http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Sep 09 '16 at 14:24
get rid of the group concat. just select user_age_list form user_detail. — xQbert, Sep 09 '16 at 14:26
Please use **Sqlfiddle:** http://sqlfiddle.com./#!9/d6515f/3 — Ramalingam Perumal, Sep 10 '16 at 05:34

Jaydip Jadhav · Accepted Answer · 2016-09-12T07:35:43.810

1

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)

edited Sep 12 '16 at 07:35

answered Sep 09 '16 at 14:29

Jaydip Jadhav

12,179
6
24
40

Return the empty result. – Ramalingam Perumal Sep 09 '16 at 14:32
what kind of data present in `user_age_list` column? – Jaydip Jadhav Sep 09 '16 at 14:34
SubQuery will return "12234" Without GROUP_CONCAT – Ramalingam Perumal Sep 09 '16 at 14:34
1

"12234" as single value or each age on separate row – Jaydip Jadhav Sep 09 '16 at 14:35
Your subquery will return this value. `user_age_list` as separate row. – Ramalingam Perumal Sep 09 '16 at 14:40
if this is so then this should work since this query become equivalent to your first query – Jaydip Jadhav Sep 09 '16 at 14:44
It's retun realy empty. – Ramalingam Perumal Sep 09 '16 at 14:48
`SELECT * FROM `user` WHERE user_age IN(1,22,34);` It return the result without subquery. – Ramalingam Perumal Sep 09 '16 at 14:49
This solution also return the empty result. – Ramalingam Perumal Sep 10 '16 at 05:15
I have gone through SqlFiddle it seem thats my first solution giving result what u looking for – Jaydip Jadhav Sep 12 '16 at 07:04
Sorry I was try to UPDATED query only. It's working fine. Thanks! – Ramalingam Perumal Sep 12 '16 at 07:28

score 0 · Answer 2 · answered Sep 09 '16 at 14:28

0

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

You don't need the group concat here. The engine knows the result set is an inclusive list without the group concat.

What it is trying to do is compare the '1,22,34' to each user_age which is why you get no results.

1 <> '1,22,34'
22 <> '1,22,34'
34 <> '1,22,34'

thus no results.

answered Sep 09 '16 at 14:28

xQbert

34,733
2
41
62

It's return the empty rows.It consider the single value within single quotes. – Ramalingam Perumal Sep 09 '16 at 14:30
What is the result if you `SELECT * FROM user_detail WHERE user_age_list IN(1,22,34)`;? It seems to me the problem may be how the data is structured in that table then... – xQbert Sep 09 '16 at 14:34
I am getting this result: `1,22,34` – Ramalingam Perumal Sep 09 '16 at 14:42
Then the where clause of `WHERE ID = '1'` is causing no records to be returned from the subquery. Why do you need ID=1 in the user_age_list? At this point I would need to see this behavior as a SQLFiddle.com. I can't imange why this or @JayDipJ 's solution is not working – xQbert Sep 09 '16 at 14:44

MySql - SubQuery is not working properly

2 Answers2