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?