homepage Welcome to WebmasterWorld Guest from 50.19.206.49
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Queries with multiple sets of aggregate data
noyearzero




msg:4467208
 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


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


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

 

coopster




msg:4472740
 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