Solved

How To select all values not included in a join

Posted on 2009-04-07
5
163 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
  • 2
  • 2
5 Comments
 
LVL 12

Expert Comment

by:udayakumarlm
Comment Utility
use select tbl1.value from tbl1 join tbl2
on not (tbl1.value = tbl2.value)
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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 142

Accepted Solution

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

please try mine.
0
 

Author Comment

by:jmvega00
Comment Utility
thank you angel, your code did the trick.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now