Filters the Name which is not available in the different Tables

Question

0.00/5 (No votes)

See more:

I have 2 Different tables A and B. In Table A, i have id,name and in Table B, i have id,name and have some values in both tables.

For Ex:

Id Name
F001 A
F002 B
F003 C
F004 D
F005 E

Id Name
F002 B
F001 A
F005 E

Now, i have to get the missing values in table B from table A.
Is there any sqlite query for that. If no, Please give me the Solution for this case.

Ans Should be like this.

Id Name
F003 C
F004 D

Thanks in advance.

Posted 12-Jan-16 3:21am

TinkerBelly

Updated 17-Jan-16 23:05pm

v2

Add a Solution

Comments

ZurdoDev 12-Jan-16 10:00am

What is considered a missing value? The combination of Id and Name? Or just Name? Either way, this is pretty simple. Where are you stuck?

TinkerBelly 18-Jan-16 5:06am

I have included the Question here. Please Check. Thanks for your reply.

3 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)

OriginalGriff · Answer 1 · 2016-01-12T04:06:00

Solution 1

Try:

SQL

SELECT A.ID, A.Name FROM TableA A
LEFT JOIN TableB B ON B.name = A.name
WHERE B.ID IS NULL

Posted 12-Jan-16 4:06am

OriginalGriff

Comments

John C Rayan 12-Jan-16 10:12am

Looks the best solution to me. 5!

[no name] 12-Jan-16 10:14am

A 5 for this. LEFT JOIN is usually much more performant then IN/EXISTS.

ZurdoDev · Answer 2 · 2016-01-12T04:06:00

Solution 2

I reread your title and realized you just want to know which names are not in TableA that are in TableB.

For example:

SQL

SELECT Name
FROM TableB
WHERE Name NOT IN (SELECT NAME FROM TableA)

Posted 12-Jan-16 4:06am

ZurdoDev

Richard Deeming · Answer 3 · 2016-01-12T04:07:00

Solution 3

Try the EXCEPT Operator[^]:

SQL

SELECT
    ID,
    Name
FROM
    TableA

EXCEPT

SELECT
    ID,
    Name
FROM
    TableB
;

If you just want the missing names:

SQL

SELECT
    Name
FROM
    TableA

EXCEPT

SELECT
    Name
FROM
    TableB
;

Posted 12-Jan-16 4:07am

Richard Deeming

Updated 12-Jan-16 4:10am

v2