homepage Welcome to WebmasterWorld Guest from 54.243.12.156
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
MySQL - MAX in LEFT JOIN TABLE
trying to get MAX of associated records in LEFT table
ManMountain




msg:4382493
 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.

 

ovideuss




msg:4382499
 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.

ManMountain




msg:4382505
 11:04 am on Nov 2, 2011 (gmt 0)

Great Ovideuss that did the trick thanks.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved