how to get the data that does not exits in another table sql server 2005

Question

0.00/5 (No votes)

See more:

Hello Friends
I want to get the data that does not exits in another table

eg. suppose i have two table A and B which contains
Table A Table B
srprefix srno prefix no
pp/10 1 pp/10 1
pp/10 2 pp/10 2
pp/10 3 pp/10 3
pp/09 3

pls help me how could i get the output which does not exits in Table A
checking both the prefix and no fields

i.e pp/09 3

i used join but it does not work properly

Thank u

Posted 9-Sep-10 0:26am

Sagar Khairnar 55

Updated 9-Sep-10 0:47am

v3

Add a Solution

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)

JV9999 · Answer 1 · 2010-09-09T00:29:00

Solution 1

The following query does the trick if the srno and no columns have the same content:

select * from B where no NOT IN (SELECT srno from A)

Posted 9-Sep-10 0:29am

JV9999

Comments

Sagar Khairnar 55 9-Sep-10 6:40am

HI SIR,
thank u for giving me the solution but i want to check both the fields prefix and no.
Table A Table B
srprefix srno prefix no
pp/10 1 pp/10 1
pp/10 2 pp/10 2
pp/10 3
pp/09 3
i want the output as
pp/10 3

JV9999 · Answer 2 · 2010-09-09T00:45:00

Solution 2

A quick solution for that would be:

select * from B where no+''+prefix NOT IN (SELECT srno+''+srprefix from A)

NOTE: I did not test this. So you might get some errors (and probably you can also remove the '' concatenation)

Posted 9-Sep-10 0:45am

JV9999

Updated 9-Sep-10 0:46am

v2

Comments

Sagar Khairnar 55 9-Sep-10 6:52am

No Sir ,
thats not working ,
i also tried

SELECT prefix,doc_no
FROM PCHALLAN
WHERE doc_no
NOT IN (SELECT chln_prefix+chln_no FROM PURBILL)
AND prefix NOT IN (SELECT chln_prefix FROM PURBILL)
but not working
pls help me

Sagar Khairnar 55 · Answer 3 · 2010-09-09T01:17:00

Solution 3

OK THE PROBLEM IS SOLVED

I USED EXCEPT KEYWORD

--TABLE PURCHASE CHALLAN
SELECT chln_prefix,chln_no
FROM PC
EXCEPT

--TABLE PURCHASE BILL
SELECT prefix,doc_no
FROM PB

Posted 9-Sep-10 1:17am

Sagar Khairnar 55