Click here to Skip to main content
14,575,874 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have two tables
TABLE1 with two columns
Col1 Col2
A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2
C 3


TABLE 2 with two columns
Col1 Col2
A 1
B 2
B 3

I want output as data of the first table with ignoring data from the second table. The data in TABLE1 is in the combination of two columns Col1 and Col2

Output
Col1 Col2
A 2
A 3
B 1
C 1
C 2
C 3

What I have tried:

I have tried some solutions

SELECT Col1, Col2 FROM Table1 t1
inner join Table2 t2 on
t1.Col1 <> (t2.Col1)
and
t1.Col2 <> (t2.Col2)

Also, I tried left join and extract but not got the correct solution for it.
Could someone please provide any solution for the above query. Thanks in advance.
Posted
Updated 28-Nov-18 20:00pm
Rate this:
Please Sign up or sign in to vote.

Solution 1

Um, there are the EXCEPT and INTERSECT operators[^]:
SELECT * FROM Table1
EXCEPT SELECT * FROM Table2
   
Comments
Member 14071121 29-Nov-18 2:17am
   
It worked, Thank you !
OriginalGriff 29-Nov-18 2:26am
   
You're welcome!
Rate this:
Please Sign up or sign in to vote.

Solution 3

Try this
DECLARE @TABLE1 TABLE (
  Col1 VARCHAR(50), 
  Col2 int
)

DECLARE @TABLE2 TABLE (
  Col1 VARCHAR(50), 
  Col2 int
)

INSERT INTO	 @TABLE1
SELECT 'A' AS Col1, 1 AS Col2 UNION ALL
SELECT 'A' AS Col1, 2 AS Col2 UNION ALL
SELECT 'A' AS Col1, 3 AS Col2 UNION ALL
SELECT 'B' AS Col1, 1 AS Col2 UNION ALL
SELECT 'B' AS Col1, 2 AS Col2 UNION ALL
SELECT 'B' AS Col1, 3 AS Col2 UNION ALL
SELECT 'C' AS Col1, 1 AS Col2 UNION ALL
SELECT 'C' AS Col1, 2 AS Col2 UNION ALL
SELECT 'C' AS Col1, 3 AS Col2

INSERT INTO	 @TABLE2
SELECT 'A' AS Col1, 1 AS Col2 UNION ALL
SELECT 'B' AS Col1, 2 AS Col2 UNION ALL
SELECT 'B' AS Col1, 3 AS Col2

SELECT TABLE1.Col1,TABLE1.Col2
FROM (
		SELECT *, (Col1 + '_' + CONVERT(VARCHAR(50),Col2)) AS MultiCol
		FROM @TABLE1 ) AS TABLE1
WHERE TABLE1.MultiCol NOT IN  ( SELECT (Col1 + '_' + CONVERT(VARCHAR(50),Col2)) AS MultiCol
								FROM @TABLE2 )
								
-- OR

SELECT *
FROM @TABLE1
WHERE (Col1 + '_' + CONVERT(VARCHAR(50),Col2)) 
		NOT IN  ( SELECT (Col1 + '_' + CONVERT(VARCHAR(50),Col2)) AS MultiCol
				  FROM @TABLE2 )
   
Comments
Member 14071188 29-Nov-18 2:06am
   
SELECT * FROM Table1
EXCEPT SELECT * FROM Table2
Member 14071121 29-Nov-18 2:17am
   
I got the solution on it. Thanks for your solution
Richard Deeming 29-Nov-18 7:51am
   
That's a horribly inefficient way of doing it!

Even if you couldn't use EXCEPT, a NOT EXISTS clause would be better:
SELECT *
FROM @Table1 As T1
WHERE NOT EXISTS
(
    SELECT 1
    FROM @Table2 As T2
    WHERE T2.Col1 = T1.Col1
    And T2.Col2 = T1.Col2
)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100