Q

Clients with and without most recent order date

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


This was first published in June 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close