MySQL - Using query results in another UNION query

Question

How can I use results that I got from one query to another query that I'm joining with UNION? I'm trying to remove the root link and all of its children with a single use of SQL query. I could of course get the result, then with PHP make another query call, but prefer not to do it this way if possible. So far I've came to this code that doesn't work:

SELECT id AS layer1
FROM AdminNavigation
WHERE id = 1

UNION

SELECT id AS layer2
FROM AdminNavigation
WHERE parentId = layer1

UNION

SELECT id AS layer3
FROM AdminNavigation
WHERE parentId = layer2

From the looks of it I think you want nested children but I am not sure. Can you add sample data and expected output so it can be clear what you want. — Mahesh
– Mahesh, Commented Jun 9, 2017 at 10:08

Sagar Gangwal · Accepted Answer · 2017-06-09 10:22:03Z

1

SELECT id AS layer1
FROM AdminNavigation
WHERE id = 1

UNION

SELECT id AS layer2
FROM AdminNavigation
WHERE parentId in(SELECT id
FROM AdminNavigation
WHERE id = 1
)

UNION

SELECT id AS layer3
FROM AdminNavigation
WHERE parentId in (
SELECT id
FROM AdminNavigation
WHERE parentId in(SELECT id
FROM AdminNavigation
WHERE id = 1
))

You cant simply put inner query as simple resolution. As i had tried in above query.

Hope this will helps

edited Jun 9, 2017 at 10:22

answered Jun 9, 2017 at 10:10

Sagar Gangwal

8,1303 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Donatas Over a year ago

Thank you this worked (though was lacking last bracket at the end).

Collectives™ on Stack Overflow

MySQL - Using query results in another UNION query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related