Search code examples
mysqljoininner-joinwhere-clause

Mysql how can i sql two tables and count groupid from the second

I'm trying to make an Sql , that get all data from first table "posts_main" and then get the count of comments from second table "posts_comments"

I tried: 

$sql = "SELECT * FROM posts_main, count(posts_comments.groupid)
INNER JOIN posts_comments ON posts_comments.groupid = posts_main.id
WHERE posts_main.user_id = '$user_id'
GROUP BY posts_main.id";

Please, how can i do that ?

thanks....

Solution

  • Try with below query.

    $sql = "select posts_main.*, 
(select groupid from posts_comments where groupid = posts_main.id group by groupid ) as count_group 
from posts_main 
WHERE posts_main.user_id = '$user_id' ";