Use value from first select for second select in union?

Question

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.

This is called a hierarchical query. This may be the answer: http://stackoverflow.com/questions/8104187/mysql-hierarchical-queries. Make sure to read the warning in the comment, though. This can be dangerous. — Robbert, Dec 05 '14 at 22:56
I don't understand how you got your expected results? You want all rows with the tag name 'object' (which is row 4) and you want to get rows with that same parent_id? So why aren't you only returning object and class? — AdamMc331, Dec 06 '14 at 02:49
Since I select object initially in the first select, I need to then select the parent (php) as a result of that first query. I know the example query is wrong so I'm looking for guidance on how to correct it. — EternalHour, Dec 06 '14 at 07:33

score 2 · Answer 1 · edited Dec 06 '14 at 02:50

2

Try this:

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

edited Dec 06 '14 at 02:50

AdamMc331

16,492
10
71
133

answered Dec 05 '14 at 23:11

anupam.singhal

101
3

Just selects the same row twice. – EternalHour Dec 05 '14 at 23:25

Saharsh Shah · Accepted Answer · 2014-12-06T07:45:23.180

2

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 |

edited Dec 06 '14 at 07:45

answered Dec 06 '14 at 06:49

Saharsh Shah

28,687
8
48
83

No result on that query. – EternalHour Dec 06 '14 at 07:36

score 0 · Answer 3 · answered Dec 06 '14 at 03:05

0

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

answered Dec 06 '14 at 03:05

Andras

2,995
11
17

1

The problem with the query I used as an example, is that the second select doesn't recognize the child alias from the first select. – EternalHour Dec 06 '14 at 07:40

Use value from first select for second select in union?

3 Answers3