why this not in query is not working

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

SQL

SELECT REGISTRATIONNO FROM VEHICLE_MASTER WHERE REGISTRATIONNO
NOT IN (SELECT REGISTRATIONNO FROM VEHICLE_SUPERUSER where superuserid='demo')

the problem here is if i execute the query its displaying all the registration no from the table VEHICLE_MASTER which is incorrect,

but if i exceute the

SQL

SELECT REGISTRATIONNO FROM VEHICLE_SUPERUSER where superuserid='demo'

separately its displaying the REGISTRATIONNO of super user id "demo".

it should display the 'not in' values alone, but its displaying all values

the "not in" keyword is not working.

Show me all the records from VEHICLE_MASTER except for those records that exist in VEHICLE_SUPERUSER where the superuserid is 'demo'"

Posted 22-Nov-11 22:08pm

ashok_89

Updated 22-Nov-11 23:12pm

member60

v6

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)

bluesathish · Answer 1 · 2011-11-22T22:17:00

Solution 1

hi ashok,
No problem with your query, check the REGISTRATIONNO column of both the Tables having the same Datatype. Not in works only if the coulmns having the same datatypes.

Let say for ex., REGISTRATIONNO int Datatype in both Tables VEHICLE_SUPERUSER and VEHICLE_MASTER. Check this, Hope its help you...

Regards,
Sathish

Posted 22-Nov-11 22:17pm

bluesathish

Comments

ashok_89 23-Nov-11 4:24am

no i checked they are same

Pandya Anil · Answer 2 · 2011-11-22T22:21:00

Solution 2

Use any of the following

SQL

SELECT REGISTRATIONNO FROM VEHICLE_MASTER WHERE 
NOT EXISTS (SELECT REGISTRATIONNO FROM VEHICLE_SUPERUSER where superuserid='demo');

SELECT REGISTRATIONNO FROM VEHICLE_MASTER VM WHERE 
LEFT OUTER JOIN VEHICLE_SUPERUSER VSU ON VM.REGISTRATIONNO = VSU.REGISTRATIONNO WHERE VSU.superuserid != 'demo';

Posted 22-Nov-11 22:21pm

Pandya Anil

Updated 22-Nov-11 22:22pm

v3

Comments

ashok_89 23-Nov-11 4:32am

the left outer join showing error

Dylan Morley · Answer 3 · 2011-11-22T22:23:00

Solution 3

Your query is

SQL

SELECT REGISTRATIONNO FROM VEHICLE_MASTER WHERE REGISTRATIONNO
NOT IN (SELECT REGISTRATIONNO FROM VEHICLE_SUPERUSER where superuserid='demo')

So, what you are saying is

"Show me all the records from VEHICLE_MASTER except for those records that exist in VEHICLE_SUPERUSER where the superuserid is 'demo'"

Going by what you say here..

it should display the 'not in' values alone, but its displaying all values

..the NOT IN syntax isn't what you want. If you want to display only those values, then you should use something like

SQL

SELECT 
* 
FROM VEHICLE_MASTER 
WHERE REGISTRATIONNO 
IN (SELECT REGISTRATIONNO FROM VEHICLE_SUPERUSER where superuserid='demo')

Posted 22-Nov-11 22:23pm

Dylan Morley

Comments

ashok_89 23-Nov-11 4:31am

vehicle_master vehicle_superuser
registrationno registrationno superuserid
1234 256 opr
12 demo
256

ashok_89 23-Nov-11 4:31am

here i should get 1234 ,but iam getting 1234,256.

Dylan Morley 23-Nov-11 4:41am

Of course you do, because you've used the NOT IN syntax.

You are SELECTING the registrationno where userid = 'demo' which is 12. Neither of the records in VEHICLE_MASTER are 12, which is why both are displayed

Your data is nonsense really, you should be enforcing some sort of referential integrity between the 2 tables

ashok_89 23-Nov-11 4:47am

SORRY THATS NOT "OPR" IT IS ALSO "DEMO" IAM AN BEGGINER SIR THATHS WHY

ashok_89 23-Nov-11 5:05am

no sir the IN will display all the records of the VEHICLE_SUPERUSER

bluesathish · Answer 4 · 2011-11-30T23:44:00

Solution 4

Hi Ashok,
Check this Query it'll surely give you the exact results,

SQL

SELECT VM.REGISTRATIONNO FROM VEHICLE_MASTER VM INNER JOIN  VEHICLE_SUPERUSER VSU
ON VM.REGISTRATIONNO <> VSU.REGISTRATIONNO AND VSU.SUPERUSERID='demo'

Posted 30-Nov-11 23:44pm

bluesathish