MySQL - SELECT ... (Subquery) AS fieldname

Question

I currently have one MySQL table of posts for a forum. This includes parent posts and child posts. If it is a parent post, it has a topic. If it is a child post, it includes a parent_id.

E.g.:

TABLE posts

+----+-----------+---------+--------+------+
| id | parent_id |  topic  | author | body |
+----+-----------+---------+--------+------+
|  1 |      NULL | "Hello" |  "Me"  | ...  |
|  2 |         1 |  NULL   |  "Me"  | ...  |
+----+-----------+---------+--------+------+

I would like to run one SQL query, similar to the following:

SELECT id, 'self' AS parent_id, topic, author, body FROM posts WHERE parent_id IS NULL,
UNION
SELECT id, parent_id, (SELECT topic WHERE id=parent_id) as topic FROM posts WHERE topic IS NULL

Desired output:

+----+-----------+---------+--------+------+
| id | parent_id |  topic  | author | body |
+----+-----------+---------+--------+------+
|  1 |    "self" | "Hello" |  "Me"  | ...  |
|  2 |         1 | "Hello" |  "Me"  | ...  |
+----+-----------+---------+--------+------+

Basically, I want to return the parent topic without having to run multiple queries. Is there any way to do this? Should I just add a "parent_topic" field instead?

Thanks!

You can use case, `CASE WHEN parent_id IS NULL THEN 'self' ELSE parent_id END`. — user2989408, Feb 07 '14 at 20:59
My issue lies with returning the "topic" field from the parent post :) — Trey, Feb 07 '14 at 21:01

score 1 · Accepted Answer · answered Feb 07 '14 at 21:07

1

The following SQL will do what you're looking for:

select p_child.id, if(p_child.parent_id is null, 'self', p_child.parent_id) as parent_id, if(p_parent.id is null, p_child.topic, p_parent.topic) as topic, p_child.author, p_child.body
from posts p_child
    left join posts p_parent on p_child.parent_id = p_parent.id;

answered Feb 07 '14 at 21:07

Tim Burch

1,088
7
9

Thanks! Had to finagle a bit to get it to fit my model (should have just used my model verbatim) but this did the trick! Any specific reason why you noted that a sub-query should be avoided? – Trey Feb 07 '14 at 21:28
1

For performance reasons, sub-queries should be avoided if at all possible (see http://stackoverflow.com/questions/2577174/join-vs-sub-query for some discussion of the subject). – Tim Burch Feb 07 '14 at 21:31

score 0 · Answer 2 · answered Feb 07 '14 at 21:04

0

Try this query, if the parent_id is not null, I am selecting the parents' topic in a sub-query. I am using case to do that.

SELECT id,
    CASE WHEN parent_id IS NULL THEN 'SELF'
         ELSE parent_id
    END as parent_id
    CASE WHEN parent_id IS NOT NULL THEN (SELECT t.topic FROM posts as t WHERE t.id = m.parenyt_id)
        ELSE topic
    END as Topic,
    author,
    body
FROM posts as m

answered Feb 07 '14 at 21:04

user2989408

3,127
1
17
15

A sub-query isn't required here, and should therefore be avoided. – Tim Burch Feb 07 '14 at 21:08

MySQL - SELECT ... (Subquery) AS fieldname

2 Answers2