Menu
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!
488
asked Oct 30 '12 15:10
You can have multiple conditions in your ON clause using AND , OR , etc.
Here when it comes to Left Join in SQL it only returns all the records or tuples or rows from left table and only those records matching from the right table. Syntax For Left Join: SELECT column names FROM table1 LEFT JOIN table2 ON table1.
Again, if we perform a left outer join where date = date, each row from Table 5 will join on to every matching row from Table 4. However, in this case, the join will result in 4 rows of duplicate dates in the joined DataSet (see Table 6).
Yes, it is possible. We would use a query with two LEFT OUTER JOINs to retrieve the hierarchy.
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
130
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
