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

Return all not matching

I have a table of orders and I have a table of packingslips...

the packingslips contain a foregin key linking to the orders table.

I want to return all the orders that are not linked to a packingslip.

I tried this...but it just returns all the orders...

select orders.id from orders
left outer join packingslips on orders.id = packingslips.orderid

Any ideas?
0
copyPasteGhost
Asked:
copyPasteGhost
  • 2
  • 2
1 Solution
 
pivarCommented:
Hi,

Try

select orders.id from orders
where not exists (select 1 from packingslips where orders.id = packingslips.orderid)

/peter

0
 
copyPasteGhostAuthor Commented:
yes that works.

I was looking to learn how to use the left outer joins (still not sure what to use it for) if you can shed a little light on that, that would be great.

but you are correct your solution does work thanks!
0
 
pivarCommented:
Use a left join if you want to retrieve rows from orders even though there is no match when joining packingslips. The columns from packingslips will be null if there is no match.
0
 
MilleniumaireCommented:
You can acheive the same using your original outer join but written as follows:

select orders.id
from orders
left outer join packingslips on orders.id = packingslips.orderid
where packingslips.orderid is null

You missed out the check for null in your original query.  The left outer join returns all rows from the orders table even if a row isn't found in the packingslips table.  However, for each row returned, any column values taken from packingslips will be null, so to only retrieve those that don't exist filter by the packingslips orderid being null.

This can be seen better in the following query which returns all orders and some packingslip details:

select orders.id, packingslips.orderid
from orders
left outer join packingslips on orders.id = packingslips.orderid
0
 
copyPasteGhostAuthor Commented:
wow thanks! that's what I was after...
Thanks for your help. Very cool of you.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

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