How to make a join that not repeat parent values

Question

I'm trying make a SQL that not repeat parent values.

Query:

select 
  p.name as parent_name,
  c.name as children_name
from 
  parent p left outer join children c using(parent_id);

The result is:

PARENT_NAME | CHILDREN_NAME
Parent 1    | Children 1
Parent 1    | Children 2
Parent 1    | Children 3
Parent 1    | Children 4
Parent 2    | Children 1
Parent 2    | Children 2
Parent 2    | Children 3
Parent 2    | Children 4

But, my expectation is (like a drilldown):

PARENT_NAME | CHILDREN_NAME
Parent 1    | Children 1
<NULL>      | Children 2
<NULL>      | Children 3
<NULL>      | Children 4
Parent 2    | Children 1
<NULL>      | Children 2
<NULL>      | Children 3
<NULL>      | Children 4

The SQLFiddle with example is: http://sqlfiddle.com/#!7/2e8f4/1

Answer 1

You can use row_number() and a case expression:

select 
    case when row_number() over(partition by p.parent_id order by c.id) = 1 then p.name end as parent_name,
    c.name as children_name
from parent p 
left outer join children c using(parent_id)
order by p.parent_id, c.id

If your version of SQLite does not support window functions, one alternative is a correlated subquery:

select 
    case when not exists (select 1 from children c1 where c1.parent_id = c.parent_id and c1.id < c.id) then p.name end as parent_name,
    c.name as children_name
from parent p 
left outer join children c using(parent_id)
order by p.parent_id, c.id