Sub Query Returns more than 1 Row

[Ranek]

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 = "[e-mail]" 
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 = "[e-mail]" 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 = "[e-mail]" AND wp_wpa_bids.bidder_email = "[e-mail]" 
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 = "[e-mail]" 
AND wp_wpa_bids.bidder_email = "[e-mail]" 
GROUP BY wp_wpa_auctions.id

Where am i going wrong?

[sufyan2011]

Forgive me if I am wrong but try to remove "GROUP BY auction_id" clause from inner query:

SELECT count(*) FROM wp_wpa_bids WHERE bidder_email = "[e-mail]" GROUP BY auction_id

[Biiim]

Sounds a very complex way of doing a simple thing.

You want to select from the auction table as the base table for your query

SELECT * FROM `wp_wpa_auctions` AS `auctions`

Then Join on the bids table for who has bidded on what

LEFT JOIN `wp_wpa_bids` AS `bids` ON `auctions`.`auction_id` = `bids`.`auction_id`

Now you want to limit it to a specific user by his id preferably but a unique email would be fine

WHERE `bids`.`bidder_email` = '[e-mail]'

The data you are looking is bids, you don't want any duplicate bids so doing a group by will prevent this

GROUP BY `bids`.`bid_id`

So all together

SELECT * FROM `wp_wpa_auctions` AS `auctions`
LEFT JOIN `wp_wpa_bids` AS `bids` ON `auctions`.`auction_id` = `bids`.`auction_id`
WHERE `bids`.`bidder_email` = '[e-mail]'
GROUP BY `bids`.`bid_id`