i have to SQL server 2008 tables as below
我必须如下SQL Server 2008表
Table A
表A.
ID int Not Null (primary ID)
No int NULL
Value int NULL
Flag nchar(10) NULL
Table B
表B.
ID int Not Null (primary ID)
No int NULL
Value int NULL
Flag nchar(10) NULL
and i Have below data in table A
我在表A中有以下数据
ID No Value Flag
1 1 12 1
2 1 12 1
3 1 25 1
4 2 120 1
5 3 36 2
6 2 120 2
7 6 1 1
8 2 10 1
9 6 10 2
10 1 25 2
11 2 120 1
and there no records in table B when i write below statement
当我写下面的陈述时，表B中没有记录
SELECT dbo.A.No, SUM(dbo.A.Value) AS [IN], SUM(ISNULL(dbo.B.Value, 0)) AS OUT
FROM dbo.A LEFT OUTER JOIN
dbo.B ON dbo.A.NO = dbo.B.NO
WHERE (dbo.A.Flag = N'1')
GROUP BY dbo.A.No
I am getting below result
我的结果低于结果
No IN OUT
1 49 0
2 250 0
6 1 0
When I add WHERE (dbo.A.Flag = N'1') AND (dbo.B.Flag = N'1')
当我添加WHERE（dbo.A.Flag = N'1'）AND（dbo.B.Flag = N'1'）
nothing is coming..
一切都没有......
my question is How to get records from table B as 0 when B not contains records or not find B.Id
我的问题是当B不包含记录或未找到B.Id时，如何将表B中的记录设为0
UPDATE : When i have data in table B Then records are coming.
更新：当我在表B中有数据时，记录即将到来。
2 个解决方案
#1
5
Imran,
I think the below sql will help you.
伊姆兰，我认为下面的SQL会帮助你。
SELECT A.No,
SUM(A.Value) AS [IN],
SUM(ISNULL(B.Value, 0)) AS [OUT]
FROM dbo.A A
LEFT JOIN dbo.B B ON A.No = B.No AND B.Flag = N'1'
WHERE A.Flag = N'1'
GROUP BY A.No
#2
-1
Try using this condition :-
尝试使用这个条件： -
WHERE (dbo.A.Flag = N'1') AND (dbo.B.Flag = ISNULL(N'1',0));
This might be helpful to you.
这可能对您有所帮助。
#1
5
Imran,
I think the below sql will help you.
伊姆兰，我认为下面的SQL会帮助你。
SELECT A.No,
SUM(A.Value) AS [IN],
SUM(ISNULL(B.Value, 0)) AS [OUT]
FROM dbo.A A
LEFT JOIN dbo.B B ON A.No = B.No AND B.Flag = N'1'
WHERE A.Flag = N'1'
GROUP BY A.No
#2
-1
Try using this condition :-
尝试使用这个条件： -
WHERE (dbo.A.Flag = N'1') AND (dbo.B.Flag = ISNULL(N'1',0));
This might be helpful to you.
这可能对您有所帮助。