August 12, 2008 at 4:13 pm
Hello,
I have 2 tables with values:
Table A
Material Plant
8843NEW 1001
8843NEW 1002
8843NEW 1003
8843NEW 1004
8843NEW 1005
8837NEW 1002
8837NEW 1003
8837NEW 2001
8837NEW 2002
Table B
Material Plant
8843NEW 1001
8843NEW 1002
8843NEW 1003
8843NEW 1004
8843NEW 2001
8837NEW 1007
8837NEW 1002
8837NEW 2001
now I need to findout the materials that are not in the same Plant as in the other table
I need the following Result:
Material Plant
8843NEW 1005
8837NEW 1003
8837NEW 2002
8843NEW 2001
8837NEW 1007
PROBLEM:
I am getting the first 3 results using the following SQL Statement:
Select * from table_a T2
where not exists
(Select * from table_b T1 where T2.material =
T1.material and T2.plant = T1.plant )
I am getting the next 2 results using the following SQL Statement:
Select * from table_b T2
where not exists
(Select * from table_a T1 where T2.material =
T1.material and T2.plant = T1.plant )
I need a single statement which can pull all the 5 records together.
Thanks in advance.
August 12, 2008 at 9:15 pm
Try...
Select * from table_a T2
where not exists
(Select * from table_b T1 where T2.material =
T1.material and T2.plant = T1.plant )
UNION ALL
Select * from table_b T2
where not exists
(Select * from table_a T1 where T2.material =
T1.material and T2.plant = T1.plant )
August 12, 2008 at 9:37 pm
You can use a full outer join.
Declare @TableA Table (Material char(7), Plant int);
Declare @TableB Table (Material char(7), Plant int);
Insert Into @TableA Values('8843NEW', 1001);
Insert Into @TableA Values('8843NEW', 1002);
Insert Into @TableA Values('8843NEW', 1003);
Insert Into @TableA Values('8843NEW', 1004);
Insert Into @TableA Values('8843NEW', 1005);
Insert Into @TableA Values('8837NEW', 1002);
Insert Into @TableA Values('8837NEW', 1003);
Insert Into @TableA Values('8837NEW', 2001);
Insert Into @TableA Values('8837NEW', 2002);
Insert Into @TableB Values('8843NEW', 1001);
Insert Into @TableB Values('8843NEW', 1002);
Insert Into @TableB Values('8843NEW', 1003);
Insert Into @TableB Values('8843NEW', 1004);
Insert Into @TableB Values('8843NEW', 2001);
Insert Into @TableB Values('8837NEW', 1007);
Insert Into @TableB Values('8837NEW', 1002);
Insert Into @TableB Values('8837NEW', 2001);
Select coalesce(a.Material, b.Material) As Material
,coalesce(a.Plant, b.Plant) As Plant
From @TableA a
Full Outer Join @TableB b
On b.Material = a.Material
And b.Plant = a.Plant
Where (a.Material Is Null Or b.Material Is Null);
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 13, 2008 at 8:40 am
Many thanks...
August 13, 2008 at 8:57 am
Both solutions are working.. Never read about "coalesce". Reading more about it now. Thanks for introducing a new command.
August 13, 2008 at 9:42 am
Manosh (8/13/2008)
Both solutions are working.. Never read about "coalesce". Reading more about it now. Thanks for introducing a new command.
You are welcome 🙂
COALESCE is the ANSI standard version of ISNULL. The major difference between the two is that coalesce can accept multiple expression (e.g. COALESCE(col1, col2, col3..., n)) where ISNULL can only accept two expressions.
How each determines the data type returned is also a difference that you should be aware of, but for the most part they work the same.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply