Selecting a value from another row: mysql

Question

I have 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.

Do you know if this would be more than one level deep? As is already discussed, a single join would solve your problem if replies can only be added to a Post with a title (i.e. not another reply). — Garrett Bluma
– Garrett Bluma, Commented May 4, 2011 at 2:34

hsz · Accepted Answer · 2011-05-03 23:56:29Z

1

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

answered May 3, 2011 at 23:56

hsz

153k63 gold badges269 silver badges320 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

andrewmitchell · Accepted Answer · 2011-05-03 23:59:11Z

1

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)

answered May 3, 2011 at 23:59

andrewmitchell

1,61914 silver badges15 bronze badges

Comments

stevecomrie · Accepted Answer · 2011-05-04 02:28:20Z

1

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

edited May 4, 2011 at 2:28

answered May 3, 2011 at 23:55

stevecomrie

2,48320 silver badges29 bronze badges

1 Comment

jonnnnnnnnnie Over a year ago

#1052 - Column 'id' in where clause is ambiguous

Collectives™ on Stack Overflow

Selecting a value from another row: mysql

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related