php-mysql如何以正确的方式为该表运行子查询

Question

我试图查询两个彼此都有外键的表来显示条件结果，我的表如下：

auction_bids表：

id applicant_id bid auction_id owner.id

拍卖表：

id user_id title description

我需要向登录用户显示的结果是：

title applicant_id [how many bids the project has] auction_id

我当前不工作的查询是：

SELECT (SELECT COUNT(auction_bids.owner_id)
          FROM auction_bids 
         WHERE auction_bids.owner_id = ".$_SESSION['userid']."
       ) AS count, auction_bids.* 
  FROM auction_bids 
 WHERE auction_bids.owner_id = ".$_SESSION['userid']."

我无法在数据库中获得每次拍卖的出价和标题感谢

Answer 1

此项将列出所有申请人及其在所选用户的所有拍卖中的总出价：

   SELECT a.title, b.applicant_id, COUNT(b.applicant_id) AS bids, a.id
     FROM auction_bids b
     JOIN auctions a ON a.id = b.auction_id
    WHERE b.owner_id = ".$_SESSION['userid']."
 GROUP BY b.applicant_id

请在此处观看现场演示

请记住，在你的问题上，你有这样的owner.id，而在这里我有一个带下划线的owner_id，根据需要更改它。

根据你对另一条回复的评论，你可能希望每次拍卖的结果与你对问题的建议不同。

此项将列出用户的所有拍卖，包括总出价、拍卖标题和id:

   SELECT a.title, COUNT(b.applicant_id) AS total_bids, a.id
     FROM auction_bids b
     JOIN auctions a ON a.id = b.auction_id
    WHERE b.owner_id = ".$_SESSION['userid']."
 GROUP BY b.auction_id

请在此处观看现场演示

Answer 2

我想你想要一个GROUP BY:

SELECT a.title, ab.applicant_id, COUNT(*), a.id
FROM auction a JOIN
     auction_bids ab
     ON a.id = ab.auction_id
WHERE a.user_id = ".$_SESSION['userid']."
GROUP BY a.id