LEFT JOIN in MySQL using multiple ON conditions

Question

I know how to do a LEFT JOIN in MySQL with a single conditional or with using WHERE key IS NULL, but how do you do it when you have multiple conditions in your ON statement? I have an invoice table that contains an Order Number and Invoice Date. I also have a ship table that contains an Order Number and Ship Date. I want to return any items from the invoice table where there is NOT a corresponding record in the ship table. For example...

invoice table:

InvoiceNum  OrderNum  InvoiceDate
106433        136365      2011-10-03
111888        136365      2012-06-19

ship table:

OrderNum   ShipDate
136365       2012-06-19

I want to write a query that will return just invoice number 106433 from the invoice table. Does someone know how best to do that. I am joining other tables into the query, but this is the one that I'm having trouble figuring out. Thanks for any help anyone can give!

Answer 1

You can have multiple conditions in your ON clause using AND, OR, etc.

select i.InvoiceNum
from invoice  i
left outer join ship s on i.OrderNum = s.OrderNum  
    and i.InvoiceDate = s.ShipDate
where s.OrderNum is null

SQL Fiddle Example