Click here to Skip to main content
14,421,687 members
Rate this:
Please Sign up or sign in to vote.
See more:
--I have the below scenario in my project...But i am giving a simple example. Kindly help.

table1

fields :
1) id
2) distance
3) address

table2

fields:
1) id
2) colony
3) subcolony

--question..
now a query has to be written in such a way that :
-----> it should fetch all the id from table 2 that is not in table1
-----> it should fetch all the id from table 2 that is in table1 but the distance is zero


--The idea which came to my mind at first glance is :

select id from table2 
where id not in(select id from table1)
union all 
select id from table2
where id in(select id from table1 where distance=0)


--So is there any other way to tackle. Is there any other way to write this query ?

Kindly need your help.
Posted
Updated 13-Sep-13 8:04am
v5
Comments
phil.o 13-Sep-13 11:20am
   
Your query answers exactly to the requirements you gave. So what's wrong with it ? You do not even need a 'UNION ALL' statement, as both queries definitely cannot return any common element. A simple 'UNION' does the trick.
Rate this:
Please Sign up or sign in to vote.

Solution 2

Second solution with CTE:
SELECT id FROM table2
WHERE (id NOT IN (SELECT id FROM table1))
OR (id IN (SELECT id FROM table1 WHERE distance=0))


I thought about a CTE at first glance, but it would not have removed the union.
This solution should work without UNION.
   
Comments
anurag19289 13-Sep-13 17:17pm
   
cool its working... :)
Rate this:
Please Sign up or sign in to vote.

Solution 1

This can do what you need, except you made as small mistake in the end of the query:
SELECT id FROM table2 
WHERE id NOT IN(SELECT id FROM table1)
UNION 
SELECT id FROM table2
WHERE id IN(SELECT id FROM table1 WHERE distance=0)


I replaced 'and' with 'where'.
   
Comments
anurag19289 13-Sep-13 11:18am
   
ok but union is going to give me duplicate values i guess..

and i need a query other than union or union all...
phil.o 13-Sep-13 11:21am
   
No, both queries cannot return common elements since one is 'NOT IN' and other is 'IN' -> they are mutually exclusive.
anurag19289 13-Sep-13 11:30am
   
Ok Ok...Is there any other way to write this query ?
phil.o 13-Sep-13 15:12pm
   
None that would be shorter, simpler or more efficient.
A CTE would do it, but it would be more complicated. I'll post it as a second, alternative solution.
anurag19289 13-Sep-13 15:13pm
   
ok bro..
anurag19289 13-Sep-13 15:13pm
   
kindly post that too
phil.o 13-Sep-13 15:26pm
   
Ok. And remember, if that helped you, and you had your answer, thanks to:
- post a vote
- mark your question as answered

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100