CODEWITHSUNDEEP
mysqlsqlselectjoinunion
EternalHour
EternalHour

Reputation: 8621

Use value from first select for second select in union?

Lets say I've got the following table which is a 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 to devise a query which selects the associated parent_id and tag_name based on the value of the initial select statement.

Something like this:

SELECT * FROM tags AS child
WHERE child.tag_name = 'object'
UNION
SELECT * FROM tags AS parent
WHERE parent.parent_id = child.parent_id

I need to be able to return the combined rows from both these queries which is why I'm using UNION.

The expected result should be:

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

I also think that a JOIN may work but I can't quite make it work.

Upvotes: 2

Views: 6615

Answers (3)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT tag_id, tag_name, parent_id  
FROM tags AS child
WHERE child.tag_name = 'object'
UNION
SELECT parent.tag_id, parent.tag_name, parent.parent_id  
FROM tags AS parent
INNER JOIN tags AS child ON parent.tag_id = child.parent_id AND child.tag_name = 'object';

Check the SQL FIDDLE DEMO

OUTPUT

| TAG_ID | TAG_NAME | PARENT_ID |
|--------|----------|-----------|
|      4 |   object |         2 |
|      2 |      php |         1 |

Upvotes: 2

Andras
Andras

Reputation: 3055

if you want tag_name = 'object' to return 'php', you need to equate child.parent_id to parent.tag_id (and not the parent_ids to each other)

...
WHERE parent.parent_id = child.parent_id

Upvotes: 0

anupam.singhal
anupam.singhal

Reputation: 101

Try this:

SELECT *
FROM tags t1, tags t2
WHERE t1.parent_id = t2.parent_id AND t1.tag_name = 'object';

Upvotes: 2

Related Questions