Click here to Skip to main content
15,029,944 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
how can i write in instead of not in ,in the below query
i want records in location_temp which is not in location table?
SQL
select *
from tblLocation_Temp
where tblLocation_Temp.Code
not in (
select tblLocation.Code
from tblLocation )
Posted
Updated 24-Jul-11 0:57am
v2
Comments
OriginalGriff 24-Jul-11 6:35am
   
I'm sure English is not your native language, but it is the default language for this site.
In English, your question makes no sense at all.
Please, either try to find a better translation of your question to English, or find a site in your own native language, as they may be able to help you better than we can!
[no name] 24-Jul-11 6:44am
   
He/she probably wants to rewrite this query using the 'IN' clause instead of the 'NOT IN' clause.
faezeh66 24-Jul-11 6:51am
   
exactly
[no name] 24-Jul-11 6:43am
   
What are you trying to achieve?
faezeh66 24-Jul-11 6:54am
   
i want records in locationtemp which is not in location table?
[no name] 24-Jul-11 6:55am
   
then your query is correct, go ahead and use it.
faezeh66 24-Jul-11 6:59am
   
my boss says it is better to write it with in clause...
[no name] 24-Jul-11 7:01am
   
then ask your boss to rewrite this query using 'IN' clause without changing the logic. Your query is perfect and needs no enhancement. Just make sure that the Code column in both tables are indexed.
faezeh66 24-Jul-11 7:05am
   
in one of them it is indexed...is it enough?
[no name] 24-Jul-11 7:06am
   
no, create indexes on both tables.

SQL
select *
from tblLocation_Temp
where tblLocation_Temp.Code
not in (
select tblLocation.Code
from tblLocation )


this query is perfact if " tblLocation.Code " and " tblLocation_Temp.Code " are same type column.

if tblLocation_Temp.Code is numaric or " tblLocation.Code " is varchar then your query have a casting problem . ' solve this by cast as varchar '


i hope this help
   
Comments
raj_raje 28-Jul-11 21:53pm
   
this correct for new users my 5 for you
   
it is correct.............................thanks shameel
   

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

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900