?
Solved

Return all not matching

Posted on 2009-04-24
5
Medium Priority
?
291 Views
Last Modified: 2012-05-06
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
Comment
Question by:copyPasteGhost
  • 2
  • 2
5 Comments
 
LVL 22

Accepted Solution

by:
pivar earned 2000 total points
ID: 24225765
Hi,

Try

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

/peter

0
 
LVL 13

Author Comment

by:copyPasteGhost
ID: 24225800
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
 
LVL 22

Expert Comment

by:pivar
ID: 24225922
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
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 24226114
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
 
LVL 13

Author Comment

by:copyPasteGhost
ID: 24226186
wow thanks! that's what I was after...
Thanks for your help. Very cool of you.

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

840 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