joining 3 tables using inner join in SQL server

Question

I am working with winforms in .NET 3.5 and sql server 2005 using c#.

i need to join 3 tables such as Advance_cost,room_no_info and bill using inner join...

SELECT
      bill.bill_no AS BillNo
    , COUNT(room_no_info.room_number) AS TotalRoom
    , CONVERT(VARCHAR(11), room_no_info.in_date, 106) AS InDate
    , CONVERT(VARCHAR(11), room_no_info.out_date, 106) AS OutDate
    , bill.total AS Amount
    , Advance_cost.total_amount AS Advance
    , Advance_cost.total_amount AS Paid
    , bill.Balance AS Balance
FROM room_no_info
INNER JOIN bill ON bill.bill_no = room_no_info.bill_no
INNER JOIN Advance_cost ON bill.bill_no = Advance_cost.room_bill_no 
     AND bill.Date = '26-Jun-13'
GROUP BY
      bill.bill_no
    , room_no_info.in_date
    , room_no_info.out_date
    , bill.total
    , Advance_cost.total_amount
    , bill.Balance

in advance_cost table data may be empty or no data for the date of bill.date. so third condition in join gets false so it doesn't shows some data but i need Advance_cost.total_amount=0 when third table values gets empty

is there any idea?

What is the connection between the question title and the question content? — King King, Jun 27 '13 at 07:09
@king King , see it http://stackoverflow.com/q/17290489/1427849 , did he copied the title ? :) — zey, Jun 27 '13 at 07:15
@zey yes, I was the answerer to that question and felt surprised when looking at the title of this question and more surprised when the asker is exactly him :) — King King, Jun 27 '13 at 07:18
sorry sorry... i missed to see the title of Question... and updated — Bala, Jun 27 '13 at 07:29
@Bala you should also update your query with uppercase keywords such as select -> SELECT, ... and break the lines at appropriate point, for example, FROM should start a new line, GROUP BY should do the same, ... That would help others feel easy to see your query and can give out some solution for you. — King King, Jun 27 '13 at 07:34

score 2 · Accepted Answer · edited Jun 27 '13 at 07:56

Try using LEFT JOIN and ISNULL

SELECT
      bill.bill_no AS BillNo
    , COUNT(room_no_info.room_number) AS TotalRoom
    , CONVERT(VARCHAR(11), room_no_info.in_date, 106) AS InDate
    , CONVERT(VARCHAR(11), room_no_info.out_date, 106) AS OutDate
    , bill.total AS Amount
    , ISNULL(Advance_cost.total_amount, 0) AS Advance
    , ISNULL(Advance_cost.total_amount, 0) AS Paid
    , bill.Balance AS Balance
FROM room_no_info
INNER JOIN bill ON bill.bill_no = room_no_info.bill_no
LEFT JOIN Advance_cost ON bill.bill_no = Advance_cost.room_bill_no 
     AND bill.Date = '26-Jun-13'
GROUP BY
      bill.bill_no
    , room_no_info.in_date
    , room_no_info.out_date
    , bill.total
    , Advance_cost.total_amount
    , bill.Balance

score 1 · Answer 2 · edited Jun 27 '13 at 07:55

SELECT
      bill.bill_no AS BillNo
    , COUNT(room_no_info.room_number) AS TotalRoom
    , CONVERT(VARCHAR(11), room_no_info.in_date, 106) AS InDate
    , CONVERT(VARCHAR(11), room_no_info.out_date, 106) AS OutDate
    , bill.total AS Amount
    , ISNULL(Advance_cost.total_amount, 0) AS Advance
    , ISNULL(Advance_cost.total_amount, 0) AS Paid
    , bill.Balance AS Balance
FROM room_no_info
INNER JOIN bill ON bill.bill_no = room_no_info.bill_no
LEFT OUTER JOIN Advance_cost ON bill.bill_no = Advance_cost.room_bill_no 
     AND bill.Date = '26-Jun-13'
GROUP BY
      bill.bill_no
    , room_no_info.in_date
    , room_no_info.out_date
    , bill.total
    , Advance_cost.total_amount
    , bill.Balance

joining 3 tables using inner join in SQL server

2 Answers2