• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

How To select all values not included in a join

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
jmvega00
Asked:
jmvega00
  • 2
  • 2
1 Solution
 
udaya kumar laligondlaTechnical LeadCommented:
use select tbl1.value from tbl1 join tbl2
on not (tbl1.value = tbl2.value)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
jmvega00Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the code suggested by udayakumarlm cannot work as requested.

please try mine.
0
 
jmvega00Author Commented:
thank you angel, your code did the trick.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now