Selecting a value from another row: mysql

I hav开发者_运维问答e a table called Post:

title (NULL), content, parent_id (NULL)

The title is null because I use this for threads and replies (replies do not have a title and threads do not have a parent).

Say I want to select all the replies to post x, or an n number of replies:

SELECT * FROM post
WHERE title IS NULL
AND parent_id = x

or,

SELECT * FROM post
WHERE title IS NULL
AND parent_id IS NOT NULL
LIMIT 0, 30

How can I also select the title of a reply? Say for example if I select reply number 5 and it's a reply to post id# 2 (i.e has parent_id of 2), how can I select the title of number 2?

I don't want to use a foreach loop in mysql.

Hope this makes sense.

Thank you.

---

COALESCE returns first value of an argument list that is not null.

   SELECT post.content, COALESCE(post.title, parent.title) AS title
     FROM post
LEFT JOIN post AS parent
       ON post.parent_id = parent.id
    WHERE post.parent_id = 123

---

I'd join from your post table into your post table. It's fun. (This assumes that your post table has an id column, which corresponds to parent_id)

SELECT child.*, parent.title FROM post child JOIN post parent ON (child.parent_id=parent.id)

---

Assuming your Post table structure looks like:

+-----+------------+-----------------+----------+
| id  | parent_id  | title           | content  |
+-----+------------+-----------------+----------+
| 1   |  NULL      | Post #1 Title   | ...      |
+-----+------------+-----------------+----------+
| 2   |  NULL      | Post #2 Title   | ...      |
+-----+------------+-----------------+----------+
| 3   |   1        |                 | ...      |
+-----+------------+-----------------+----------+
| 4   |   2        |                 | ...      |
+-----+------------+-----------------+----------+

You need to use a join:

SELECT
   *, parent.title AS parent_title
FROM
   post
LEFT JOIN
   post as parent ON parent.id = post.parent_id
WHERE
   post.id = 4

That would select post id=4 and also get you the title of post id=2 stored in the field parent_title