Joining of 5 Tables

Question

I need to join 5 tables to get particular bill number.

Tables are

1. bill,
2. Service_bill
3. Damage_cost
4. Extraperson_cost
5. Advance_cost

Except bill, all other tables are may store null values.

My query is like below...

select   bill.bill_no,
         bill.total,
         bill.discount,
         bill.to_be_paid,
         isnull(Service_bill.total_amt,0) as ServiceCharge,
         isnull(Damage_cost.total_amt,0) as DamageCost,
         isnull(Extraperson_cost.total_amt,0) as ExtraCost,
         isnull(Advance_cost.total_amount,0) as Advance 
from     bill 
left join Advance_cost       on bill.bill_no=Advance_cost.room_bill_no and bill.bill_no='57' 
inner join Service_bill      on bill.bill_no=Service_bill.room_bill_no 
inner join Damage_cost       on bill.bill_no=Damage_cost.room_bill_no 
inner join Extraperson_cost  on bill.bill_no=Extraperson_cost.room_bill_no

Now it returns data's, which joins conditions are getting true.

First table should have values then other tables are null only so it must be return first tables completely. But i don't know why it comes like this!

Answer 1

Thanks Neville k... i got it using outer join...

select bill.bill_no,bill.total,bill.discount,bill.to_be_paid,
       isnull(Service_bill.total_amt,0) as ServiceCharge,
       isnull(Damage_cost.total_amt,0) as DamageCost,
       isnull(Extraperson_cost.total_amt,0) as ExtraCost,
       isnull(Advance_cost.total_amount,0) as Advance 
from   bill 
left join Advance_cost           on bill.bill_no=Advance_cost.room_bill_no 
left outer join Service_bill     on bill.bill_no=Service_bill.room_bill_no 
left outer join Damage_cost      on bill.bill_no=Damage_cost.room_bill_no 
left outer join Extraperson_cost on bill.bill_no=Extraperson_cost.room_bill_no
where  bill.bill_no='57'

Answer 2

The inner joins mean that you are only finding bills for people who have records in all 4 other tables. Use outer joins instead.

Also, you are limiting the results to those items where there is a record in "advance_cost" for bill 57. That's probaly not the idea...