Using 'Not In' Two tables with Multiple Column in Sql

Question

0.00/5 (No votes)

See more:

i have Two tables,

- Table A
- Table B

Both the tables have Different Column..How can i check one table Data not exists in another table with Multi Column Condition.here is my Code..but it cant fetch unmatch data..

SQL

Select Code,Name,Size from TableA  where Code Not in (Select Code from TableB) and Name not in (Select Name from TableB) and 
SIZE not in (Select Size from TableB)

Am also using 'Not Exists'..but no Use..

SQL

Select Code,Name,Size from TableA Tb1  where not exists (Select Code from TableB Tb2 where Tb1.Code != Tb2.CODE and Tb1.Name != Tb2.Name and Tb1.Size != Tb2.SIZE)

Suggest me any other Function..Thank You...

Posted 16-Nov-13 22:34pm

MichealRay

Updated 16-Nov-13 23:01pm

v3

Add a Solution

4 solutions

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)

Jörgen Andersson · Answer 1 · 2013-11-17T01:07:00

You can use an outer join like this:

SQL

Select  Code,Name,SIZE
FROM    TableA a
left OUTER JOIN TABLEB b
    ON  a.code = b.code
    AND a.Name = b.Name
    AND a.SIZE = b.SIZE
WHERE   b.code IS NULL
OR      b.name IS NULL
OR      b.SIZE IS NULL

If you had used Oracle you could have used an IN clause like this:

SQL

Select  Code,Name,SIZE
FROM    TableA a
WHERE   (Code,Name,SIZE) NOT IN (SELECT Code,Name,SIZE FROM TableB)

<edit>I revisited because of Maciejs comment and realized what was wrong with your "Exists" query, it should look like this:

SQL

Select  Code,Name,SIZE
FROM    TableA Tb1
WHERE NOT EXISTS (
    SELECT  1
    FROM    TableB Tb2
    WHERE   Tb1.Code = Tb2.CODE
    AND     Tb1.Name = Tb2.Name
    AND     Tb1.Size = Tb2.SIZE
    )

In the end it's probably getting the same plan as the join query but it's worth testing.</edit>

Maciej Los · Answer 2 · 2013-11-17T01:22:00

Solution 2

Solution1 is good and i would suggest you to read this article: Visual Representation of SQL Joins[^]. It will help you to understand how joins work.

Posted 17-Nov-13 1:22am

Maciej Los

Comments

Jörgen Andersson 17-Nov-13 14:36pm

Yes, it's a brilliant article in its simpleness.

Tom Marvolo Riddle 20-Nov-13 6:29am

5!.Thanks for suggesting this article

Maciej Los 20-Nov-13 14:02pm

You're welcome ;)

deepakdynamite · Answer 3 · 2013-11-20T00:19:00

Solution 3

Try out Merge Statement :

http://blog.sqlauthority.com/2008/08/28/sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete/[^]

Posted 20-Nov-13 0:19am

deepakdynamite

deepakdynamite · Answer 4 · 2013-11-22T01:22:00

Solution 4

SQL

Select Code,Name , Size from TableA a
where CODE NOT IN (Select CODE FROM TableB WHERE SIZE=a.SIZE) AND
SIZE NOT IN (Select SIZE FROM TableB WHERE CODE=a.CODE)

Try this

Posted 22-Nov-13 1:22am