CODEWITHSUNDEEP
mysqlsql
Ziyadin Shemsedinov
Ziyadin Shemsedinov

Reputation: 367

Mysql subquery alias

I get the unknown column error when I use subquery alias in where clause:

SELECT name, 
(SELECT id FROM category c2 WHERE c2.parent_id=23) children 
FROM category c1 WHERE c1.id IN children;

Can anybody tell why this code is not working?

Upvotes: 0

Views: 3436

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133370

I think you can do the query without subselect 

 select a.name, b.id as children  
 from category as a
 inner join category b on ( a.c1.id = b.id  and b.parent_id = 23 );

Upvotes: 0

Eduardo Yáñez Parareda
Eduardo Yáñez Parareda

Reputation: 10346

Do it the other way around: 

SELECT name, 
FROM category c1 
WHERE c1.id IN (SELECT id FROM category c2 WHERE c2.parent_id=23);

Upvotes: 2

Related Questions