I have a simple table where one column is the PK of another element of the same table, as follows:
id | something | parent_id ___________|______________|____________ 1 | colors | null 2 | red | 1 3 | green | 1 4 | blue | 1 ... | ... | ...
I wanted to get all elements that have the
parent_id of the element that matches a condition.
Let's suppose the parent element should have
id | something | parent_id ___________|______________|____________ 2 | red | 1 3 | green | 1 4 | blue | 1
After looking around on StackOverflow I have tried:
SELECT * FROM my_table WHERE parent_id=(SELECT id FROM my_table WHERE something='colors')
SELECT * FROM my_table as t1 WHERE t1.parent_id=(SELECT id FROM my_table as t2 WHERE t2.something='colors')
but I get this error:
Error: You have an error in your SQL syntax
The reason was a missing
; on the previous file that was being merged before mine. The above queries do work...
This is not my field and I know that a "solution"(?) (used by others in the project) could be:
SELECT * FROM my_table WHERE parent_id=(SELECT id FROM (SELECT * FROM my_table) AS t1 WHERE something='colors')
but I don't really like this
Thanks anyone for the help.
I know we should
LIMIT 1 or use
IN to prevent errors, I was trying to simplify the sintax as most as I could.