CODEWITHSUNDEEP
sqloracle11g
Prefijo Sustantivo
Prefijo Sustantivo

Reputation: 525

how to left join two tables in a single row (oracle 11g or standard sql)

Assume primary keys are in the form pn for parents and cn for children where n is a positive integer.

Parent (id primary key)                                       -- may not have children
Child  (id primary key, parent_id references Parent not null) -- children must have a parent

select Parent.id pid, Children.id cid 
from Parent 
left join Child on Parent.id = Child.parent_id

Results: (p1 has two children and p2 has none)

p1, c1
p1, c2
p2, null

What i want is:

p1,   c1,   c2
p2, null, null

Is there a standarized way to do this? (like some sort of join or reserved keyword)

Thanks

Upvotes: 0

Views: 43

Answers (1)

LAS
LAS

Reputation: 819

I'd use listagg for this. It returns the children in one column.

select Parent.id pid, listagg(Children.id, ', ') within group (order by     Children.id) the_children
from Parent 
     left join Child on Parent.id = Child.parent_id
group by Parent.id

Upvotes: 1

Related Questions