CODEWITHSUNDEEP
mysql
kheugel
kheugel

Reputation: 73

LEFT JOIN in MySQL using multiple ON conditions

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!

Upvotes: 6

Views: 18862

Answers (1)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171401

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

Upvotes: 11

Related Questions