How to get data from table one based on ignoring data present in table 2 with combination of two columns

Question

0.00/5 (No votes)

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 28-Nov-18 19:21pm

Member 14071121

Updated 28-Nov-18 20:00pm

Add a Solution

2 solutions

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 )

Posted 28-Nov-18 20:00pm

LEOH88

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
)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2018-11-28T19:52:00

Solution 1

Um, there are the EXCEPT and INTERSECT operators[^]:

SQL

SELECT * FROM Table1
EXCEPT SELECT * FROM Table2

Posted 28-Nov-18 19:52pm

OriginalGriff

Comments

Member 14071121 29-Nov-18 2:17am

It worked, Thank you !

OriginalGriff 29-Nov-18 2:26am

You're welcome!