CODEWITHSUNDEEP
phpmysqlsql
Mehdi Azmoudeh
Mehdi Azmoudeh

Reputation: 51

find rows with no child and if has child latest child

i have simple table like this:

with parent/child relationship... if a row is child, then it has a parent_id, else its parent_id is 0.

now i want to select all rows that either has no child (so itself) or has child, so get the latest child by created_at (and not include itself in the final results).

as an visual example you can look at this tiny picture:
http://s9.picofile.com/file/8294092118/qqq.jpg

i just want rows 24 and 27 be selected:

24 because it has no child
NOT 25 because it has child
27 because its the latest child of 25
and NOT 26, because its child of 25 also, but not the latest

Upvotes: 1

Views: 63

Answers (2)

Steve Lovell
Steve Lovell

Reputation: 2564

If the answer from @Fabricator doesn't work for you, please try this one:

select
    coalesce(
        (
         select id 
         from mytable
         where mytable.parent_id = parent.id
         order by created_at desc
         limit 1)
        ,parent.id
        ) as ID
from
    mytable parent
where
    parent.parent_id = 0

Upvotes: 0

Fabricator
Fabricator

Reputation: 12782

  • Use left join to get all parent to child pairs.
  • Ordering them by child's created_at and group by parent_id will produce the most recent child_id per parent_id.
  • Use coalesce to default to parent_id when child_id does not exist:

query:

select coalesce(child_id, parent_id)
from (
  select a.id parent_id, b.id child_id, b.created_at
  from mytable a
  left join mytable b on a.id = b.parent_id
  where a.parent_id = 0
  order by b.created_at desc) a
group by parent_id

Upvotes: 1

Related Questions