mysql - 将 count(*) 从联接添加到查询

Question

呃...我真的很难处理这些 mySQL 连接...

这就是我所追求的。我当前的查询如下所示。

SELECT profiles.photo, postings.postid, postings.text, postings.date, members.fname, members.lname, members.userid 
FROM profiles, postings, members
WHERE postings.wallid=postings.posterid 
AND postings.wallid=members.userid 
AND postings.wallid=profiles.userid 

我正在尝试添加来自另一个表的匹配记录的计数(*)。另一个表称为 likes，并且 postings.postid = likes.post_id。

我尝试过(并且没有返回结果)是这样的......

SELECT profiles.photo, postings.postid, postings.text, postings.date, members.fname, members.lname, members.userid,
(SELECT COUNT(*) FROM likes WHERE postings.postid=likes.post_id)
FROM profiles, postings, members, likes
WHERE postings.wallid=postings.posterid 
AND postings.wallid=members.userid 
AND postings.wallid=profiles.userid 

我在这里所做的是添加嵌套的 SELECT，我认为这可以解决这个问题。

基本上，我要问的是......给定我的第一个查询，我如何才能获取 likes 表中的记录数，其中 postings.postid = likes .post_id？一如既往，我们始终感谢任何帮助/提示/建议。

Answer 1

尝试一下:

 SELECT profiles.photo, postings.postid, postings.text, postings.date, members.fname,members.lname, members.userid,
 likes.like_count
 FROM profiles, members, postings
 LEFT JOIN (SELECT post_id, COUNT(*) like_count FROM likes GROUP BY post_id) as likes
      ON postings.postid=likes.post_id
 WHERE postings.wallid=postings.posterid 
 AND postings.wallid=members.userid 
 AND postings.wallid=profiles.userid