Welcome to WebmasterWorld Guest from 54.144.243.34

Forum Moderators: open

Queries with multiple sets of aggregate data

   
3:16 pm on Jun 19, 2012 (gmt 0)

5+ Year Member



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?
11:58 am on Jul 5, 2012 (gmt 0)

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



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month