Ask the Expert

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.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: