mysql join two table with MAX(value)

Question

I have this mysql code witch is working very well

SELECT uid , MAX(value) AS bidvalue FROM auction 
WHERE pid = '$pid' and max_bid=0 
GROUP BY uid 
ORDER BY bidvalue DESC 
LIMIT 1

How can I join this selection with users table ON users.id=auction.uid?

Thank you

Answer 1

I don't see any magic here:

SELECT uid, users.name, MAX(value) AS bidvalue
FROM auction 
INNER JOIN users ON users.id = auction.uid
WHERE pid = '$pid' and max_bid=0 
GROUP BY uid 
ORDER BY bidvalue DESC 
LIMIT 1

Answer 2

You may use join for that your query might look like

SELECT uid, users.name, MAX(value) AS bidvalue
FROM auction 
INNER JOIN users ON users.id = auction.uid
WHERE pid = '$pid' and max_bid=0 
GROUP BY uid 
ORDER BY bidvalue DESC 
LIMIT 1

or

SELECT uid, users.name, MAX(value) AS bidvalue
FROM auction 
OUTER JOIN users ON users.id = auction.uid
WHERE pid = '$pid' and max_bid=0 
GROUP BY uid 
ORDER BY bidvalue DESC 
LIMIT 1

with the joins details as per your need