Hey everyone,
The query i'm trying to perform shows a list of auctions that a user has bid on, name, id, date ended, current price etc. But also the number of bids that the user has placed on the time.
SELECT id, name, count(*) AS bid_count
FROM wp_wpa_bids
WHERE bidder_email = "email@email.com"
GROUP BY auction_id
The query above does what I want it to on its own.
But when integrated with the query below as a subquery it obviously returns an error as the outputted data has more than one row.
SELECT wp_wpa_auctions.id, name, date_end, current_price, wp_wpa_watchlist.watch_email, wp_wpa_bids.bidder_email,
(SELECT count(*) FROM wp_wpa_bids WHERE bidder_email = "email@email.com" GROUP BY auction_id) AS bid_count
FROM wp_wpa_auctions, wp_wpa_watchlist, wp_wpa_bids
WHERE wp_wpa_watchlist.auction_id = wp_wpa_auctions.id AND wp_wpa_watchlist.watch_email = "email@email.com" AND wp_wpa_bids.bidder_email = "email@email.com"
GROUP BY wp_wpa_auctions.id
I've also tried this but it will only return the total number of bids the user has made not the bids on a specific item.
SELECT wp_wpa_auctions.id, name, date_end, current_price, wp_wpa_watchlist.watch_email, wp_wpa_bids.bidder_email, count(*) AS bid_count
FROM wp_wpa_auctions, wp_wpa_watchlist, wp_wpa_bids
WHERE wp_wpa_watchlist.auction_id = wp_wpa_auctions.id
AND wp_wpa_watchlist.watch_email = "email@email.com"
AND wp_wpa_bids.bidder_email = "email@email.com"
GROUP BY wp_wpa_auctions.id
Where am i going wrong?