CODEWITHSUNDEEP
sqloracle-database
DESLilly
DESLilly

Reputation: 3

how left outer join works for table have null values

I have four tables: person and booking, booking_detail,room_pricing where person_id used as foreign key in the booking table and person_id is used as foreign key in the booking_detail table. where room_id is used as foreign_key in booking_detail

Supposing I want to show all the booking_id from booking table and their corresponding total income from rooms on each booking including those who doesn't exist in `` table such as id 1 ,i am using oracle database

person

person_id      name
        1      xyz
        2      abc
        3      jkl

booking

booking_id     person_id
        1          1
        2          3
        3          1

booking_details

person_id     roomid
        2     201
        3     303
        3     303

room_pricing

room_id       price
   201       $ 100
   302       $ 100
   303       $ 200

final table should be like this 

    booking_id    total
    1              0
    2            400$
    3             0

Upvotes: 0

Views: 34

Answers (2)

zealous
zealous

Reputation: 7503

Try the following using left join

select
  b.booking_id,
  coalesce(sum(total), 0) as total
from booking b

left join booking_details bd
on b.person_id = bd.person_id

left join room_pricing rp
on bd.room_id = rp.room_id

group by
  b.booking_id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You want a left join with aggregation:

select p.person_id, coalesce(sum(o.total), 0)
from person p left join
     order o
     on o.person_id = p.person_id
group by p.person_id;

Upvotes: 0

Related Questions