Self join query: how to return children and parent row?

Question

I have this table:

Content:
- id
- parent_id
- slug
- creation_date

parent_id is a foreign key pointing to the same table (content.id).

I want to select all the rows in the table that are children of a parent_id in the same table. I would like the parent row to be returned too.

Right now I have these two queries, both of which work only returning the child rows:

SELECT a.* 
FROM content a 
JOIN content b ON a.parent_id = b.id
WHERE b.slug = 'some-slug'
ORDER BY creation_date

and:

SELECT content.* 
FROM content 
WHERE content.parent_id = (SELECT id FROM content WHERE slug= 'some-slug')
ORDER BY creation_date

How can I return the child rows as well as the parent row ordered by creation_date?

Thanks!

Are you avoiding a `UNION` for some reason? – Sonny Jan 15 '15 at 18:13 — Sonny, Jan 15 '15 at 18:13

Andomar · Accepted Answer · 2015-01-16T13:13:21.753

3

select  child.*
from    Content child
join    Content parent
on      child.parent_id = parent.id
where   parent.slug = 'some-slug'
union all
select  parent.*
from    Content parent
where   parent.slug = 'some-slug'
order by
        creation_date

edited Jan 16 '15 at 13:13

answered Jan 15 '15 at 16:06

Andomar

232,371
49
380
404

1

Why are you using a subquery? – Sonny Jan 15 '15 at 18:15
1

@Sonny: Good point, I was using it for the `order by`, but an `order by` applies to the entire union anyway. Updated the answer. – Andomar Jan 16 '15 at 13:13

Self join query: how to return children and parent row?

1 Answers1