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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
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
ASKER
wow thanks! that's what I was after...
Thanks for your help. Very cool of you.
Thanks for your help. Very cool of you.
ASKER
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!