I have a Client table and an Order table with a 1 to many relationship. I would like to show all clients with and
without the most recent order date. I tried using a LEFT JOIN but I'm getting duplicate Client information.
If you're getting duplicate client information, your query is probably returning all orders per client --
select Client.ID , Client.Name , Orders.ID , Orders.OrderDate from Client left join Orders on Client.ID = Orders.ClientID
To get the client plus the date of the most recent order (which is what you wanted), use --
select Client.ID , Client.Name , max(Orders.OrderDate) as LatestOrder from Client left join Orders on Client.ID = Orders.ClientID group by Client.ID , Client.Name
Note this will return a NULL for LatestOrder if the client has no orders.
To take your example one step beyond what you wanted, let's try to get the client plus the details of the most recent order --
select Client.ID , Client.Name , Orders.ID , Orders.OrderDate from Client left join Orders on Client.ID = Orders.ClientID where Orders.OrderDate = ( select max(Orders.OrderDate) from Orders where ClientID = Client.ID )
Now we have a problem. If a particular client has no orders, the correlated subquery will return NULL. However, in the outer query, since it's a LEFT JOIN, Orders.OrderDate will also be NULL. So the WHERE clause will be testing NULL=NULL, which is false, so that client won't be selected.
The trick to get around this is to use a UNION, with one subquery getting all the clients that have orders, and the other subquery getting those that don't --
select Client.ID , Client.Name , Orders.ID , Orders.OrderDate from Client inner join Orders on Client.ID = Orders.ClientID where Orders.OrderDate = ( select max(Orders.OrderDate) from Orders where ClientID = Client.ID ) union all select Client.ID , Client.Name , null , null from Client where not exists ( select 1 from Orders where ClientID = Client.ID )
Note the first subquery is now an INNER JOIN.
For More Information
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.