Single result in a query using Left Joins

Question

I currently have a query similar to:

SELECT users.fname, users.lname, sales.date 
FROM users
LEFT JOIN sales ON users.id=sales.user

And this gives me the result of something like:

Fname | Lname | Date

Jeff  | K     | 2/12/08
Jeff  | K     | 5/18/08
Jeff  | K     | 2/22/09
Bill  | D     | 3/12/08
Bill  | D     | 12/9/08

This is what I pretty much expect since I have multiple sales linking to a user but I would like to have only one user listed (and maybe a way to pick which sales field that single result picks from) Is this possible and how would I go about doing this?

ie:

Fname | Lname | Date

Jeff  | K     | 5/18/08
Bill  | D     | 12/9/08

Answer 1

SELECT users.fname, users.lname, MAX(sales.date)
FROM users
LEFT JOIN sales ON users.id=sales.user
GROUP BY users.fname, users.lname

or if you want more fields from the sales table:

SELECT users.fname, users.lname, sales.date, sales.proft
FROM users
LEFT JOIN sales ON users.id=sales.user
AND sales.date = (SELECT MAX(sales.date) FROM sales WHERE user = users.id)

Answer 2

SELECT users.fname, users.lname, max(sales.date) as date
FROM users
LEFT JOIN sales ON users.id=sales.user
group by users.fname, users.lname

Answer 3

You have to group By the User, and add some aggregate function on the sales date to "control" which of the possible dates to output in the querys result set...

say you want the most recent date...

Select u.fname, u.lname, Max(s.date) LastSale
FROM users u 
  LEFT JOIN sales s ON u.id=s.user
Group By u.fname, u.lname