Select query
-
Hi All
i have two table named test1 and test2
test1 has 20 records
test2 has 20 records
In both ths tables there is some similar data
I want to retrive those records which are not matches in both the tables
lets explain
Test1 Test2
1 2
2 6
3 8
4 3
I want to retrive
result
1
6
8
4
So pl help me on this
-
Select Col from dbo.test1 T1 where not exists (Select * from dbo.Test2 T2 where T1.Col = T2.Col)
-
SELECT Test1.ID
FROM Test1 LEFT JOIN Test2 ON Test1.ID = Test2.ID
WHERE Test2.ID IS NULL
UNION ALL
SELECT Test2.ID
FROM Test2 LEFT JOIN Test1 ON Test2.ID = Test1.ID
WHERE Test1.ID IS NULL
-
Oops, right answer to wrong question :
SELECT ISNULL(Test1.ID, Test2.ID) as IDs
FROM Test1 FULL OUTER JOIN Test2 ON Test1.ID = Test2.ID
WHERE Test2.ID IS NULL or Test1.ID is null
-
Can you tell me how to do the opposite - I want to pull something from 2 tables that don't have the same data.
For example, Table 1 has 10 rows - values 1 to 10. Table 2 has 10 rows - values 3-13.
I want to be able to retrieve the values not in both tables - answer should be 1,2,11,12,13.
Thanks
-
Declare @T1 Table (id int Identity(1,1) not null primary key clustered, dude tinyint null)
Declare @T2 Table (id int Identity(3,1) not null primary key clustered, dude tinyint null)
Insert into @T1 (Dude) Select top 10 null from dbo.SysObjects
Insert into @T2 (Dude) Select top 10 null from dbo.SysObjects
Select id from @T1
Select id from @T2
Select T1.id, T2.id from @T1 T1 FULL OUTER JOIN @T2 T2 ON T1.id = T2.id where T1.id is null or T2.id is null
--OR
Select COALESCE(T1.id, T2.id) AS ID from @T1 T1 FULL OUTER JOIN @T2 T2 ON T1.id = T2.id where T1.id is null or T2.id is null
-
Thank you.
-
HTH. BTW, 3 to 13 is not 10 rows, it's 11, but I guess you figured it out from the code sample
.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply