i have to SQL server 2008 tables as below
Table A
ID int Not Null (primary ID)
No int NULL
Value int NULL
Flag nchar(10) NULL
Table 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
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
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')
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
UPDATE : When i have data in table B Then records are coming.
Imran,
I think the below sql will help you.
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments