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