CODEWITHSUNDEEP
sqlmysql
kilrizzy
kilrizzy

Reputation: 2943

Single result in a query using Left Joins

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

Upvotes: 1

Views: 157

Answers (3)

longneck
longneck

Reputation: 12226

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)

Upvotes: 4

Charles Bretana
Charles Bretana

Reputation: 146469

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

Upvotes: 0

manji
manji

Reputation: 47978

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

Upvotes: 0

Related Questions