Solved

How To select all values not included in a join

Posted on 2009-04-07
5
207 Views
Last Modified: 2012-05-06
I would like to know how to select all values not included in a join:

Example:

select tbl1.value from tbl1 join tbl2
on tbl1.value = tbl2.value
results:
2, 4, 6, 8

Now how can i select all the values that are NOT in that join...
0
Comment
Question by:jmvega00
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 12

Expert Comment

by:udaya kumar laligondla
ID: 24088688
use select tbl1.value from tbl1 join tbl2
on not (tbl1.value = tbl2.value)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24088714
this will do:_
select tbl1.value 
from tbl1 left join tbl2
  on tbl1.value = tbl2.value
where tbl2.value is null

Open in new window

0
 

Author Comment

by:jmvega00
ID: 24088972
udaya:
for some reason ur code isn't working. It is doing the same as if I had done:
select tbl1.value from tbl1 join tbl2
on (tbl1.value != tbl2.value)

lets say tbl1 has: 1, 2, 3, 4, 5, 6, 7, 8
lets say tbl2 has 2, 4, 6
so the join returns 2, 4, 6
I want it to return 1, 3, 5, 7, 8

for some reason the code i'm using is returning the stuff not in the union as well as the values in the union...
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24089232
the code suggested by udayakumarlm cannot work as requested.

please try mine.
0
 

Author Comment

by:jmvega00
ID: 24089307
thank you angel, your code did the trick.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question