| 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.
|
|
|
