homepage Welcome to WebmasterWorld Guest from 54.204.94.228
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

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



 
Msg#: 4382491 posted 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#: 4382491 posted 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#: 4382491 posted 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.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved