CODEWITHSUNDEEP
c#sqlsql-serverwinforms
Bala
Bala

Reputation: 25

joining 3 tables using inner join in SQL server

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?

Upvotes: 1

Views: 1258

Answers (2)

Edper
Edper

Reputation: 9322

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

Upvotes: 2

Amit
Amit

Reputation: 15387

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

Upvotes: 1

Related Questions