在SQL Server中使用内部联接连接3个表

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

我正在使用c#在.NET 3.5和sql server 2005中使用winforms。

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

我需要使用内连接加入3个表,如Advance_cost,room_no_info和bill ...

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

在提前_cost表数据可能为空或没有数据为bill.date的日期。所以join中的第三个条件变为false所以它没有显示一些数据但是当第三个表值变空时我需要Advance_cost.total_amount = 0

is there any idea?

有什么想法吗?

2 个解决方案

#1

Try using LEFT JOIN and ISNULL

尝试使用LEFT JOIN和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

#2

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