mysql - Use value from first select for second select in union? -

lets i've got following table familiar example.

+----------------------------------+ |              tags                | +--------+-------------+-----------+ | tag_id |   tag_name  | parent_id | +--------+-------------+-----------+ |   1    | programming |    null   | |   2    |     php     |      1    | |   3    |    class    |      2    | |   4    |    object   |      2    | |   5    |    method   |      3    | +--------+-------------+-----------+ 

i'm trying devise query selects associated parent_id , tag_name based on value of initial select statement.

something this:

select * tags child child.tag_name = 'object' union select * tags parent parent.parent_id = child.parent_id 

i need able return combined rows both these queries why i'm using union.

the expected result should be:

+--------+-------------+-----------+ | tag_id |   tag_name  | parent_id | +--------+-------------+-----------+ |   2    |     php     |      1    | |   4    |    object   |      2    | +--------+-------------+-----------+ 

i think join may work can't quite make work.

try this:

select tag_id, tag_name, parent_id   tags child child.tag_name = 'object' union select parent.tag_id, parent.tag_name, parent.parent_id   tags parent inner join tags child on parent.tag_id = child.parent_id , child.tag_name = 'object'; 

check sql fiddle demo

output

| tag_id | tag_name | parent_id | |--------|----------|-----------| |      4 |   object |         2 | |      2 |      php |         1 |