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
2
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
1
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
#1
2
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
1
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