Welcome to WebmasterWorld Guest from 54.167.177.207

Forum Moderators: open

MySQL - MAX in LEFT JOIN TABLE

trying to get MAX of associated records in LEFT table

   
10:01 am on Nov 2, 2011 (gmt 0)



I have hit a brick wall on this one, hopefully someone smarter than myself could point me in the right direction?

I have an ORDERS table containing orders (surprisingly).

I also have a DOWNLOADS table. DOWNLOADS contains an ORDERID field, which is the ID of the order table.

DOWNLOADS has an ATTEMPT field. Each download creates a record in DOWNLOADS, and each record increments the value in ATTEMPT.

I just want to get the MAX value in ATTEMPT, so I can tell users what attempt they are on (or how many attempts they have left).

DOWNLOADS may or may not have any associated records in it, depending on if the user has not yet tried to download. Each download attempt, a record is created in DOWNLOADS.

So in simplistic terms I am trying to do something like:

SELECT orders.*,MAX(downloads.attempt)
FROM orders
LEFT JOIN downloads on downloads.orderid = orders.id
WHERE orders.userid = '123'

Or rather, I'm trying to get all user orders, with the MAX of download attempts where any download attempts (records in DOWNLOADS) exists - or null if there are none.

I've been going through stackoverflow all day looking at similar examples but somehow all similar examples are massively complicated. Is there a simple (or at least elegant) way of doing this?

A bit of help would be hugely appreciated.

Thanks.
10:23 am on Nov 2, 2011 (gmt 0)



SELECT orders.*,MAX(downloads.attempt)
FROM orders
LEFT JOIN downloads on downloads.orderid = orders.id
GROUP BY orders.id
HAVING orders.userid = '123'

Replace orders.id with whatever you want to group by.
11:04 am on Nov 2, 2011 (gmt 0)



Great Ovideuss that did the trick thanks.
 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month