homepage Welcome to WebmasterWorld Guest from
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

Queries with multiple sets of aggregate data

5+ Year Member

Msg#: 4467206 posted 3:16 pm on Jun 19, 2012 (gmt 0)

So basically I have an auctions table which i'm joining to a users table. from there, the user is joined to a ratings table which needs to show their average rating and there number of votes. Then, I need to join a bids table onto the auction to pull the number of bids, and the lowest bid.

I realize I can accomplish this with 4 subqueries, but I'm hoping there is a better method so I don't have to repeat the complicated where clauses for each.

My thought process was this

FROM auctions
LEFT JOIN users ON users.user_id = auctions.user_id
AVG(users_ratings.rating) AS rating,
COUNT(users_ratings.rating) AS ratings
FROM users_ratings
users_ratings.user_id = users.user_id
) AS rating ON 1
COUNT(auctions_bids.jb_id) AS bids,
MIN(auctions_bids.amount) AS low_bid,
FROM auctions_bids
auctions_bids.auction_id = auctions_bids.auction_id
auctions_bids.active = '1'
) AS bids ON 1
auctions.active = '1'

However the outer query doesn't pass any information into the subqueries in this case. any thoughts?



WebmasterWorld Administrator coopster us a WebmasterWorld Top Contributor of All Time 10+ Year Member

Msg#: 4467206 posted 11:58 am on Jul 5, 2012 (gmt 0)

Build it in pieces. The "auctions" part is easy enough, you already have that complete. So move on to your ratings and add the bids later. Once you get each section down pat you can move on to the next. It makes troubleshooting much easier.

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