Joining 3 tables MySQL

Question

I have three tables:

Table 1 : Posts: id, uid, post, created

Table 2: User: id, username, email etc

Table 3: PostLikes: id, pid, uid

I wish to create a query that'll select 10 posts along with username and no of likes for each post. I want 0 for posts which have no likes. I have inserted one record in the likes table for say post id 50. Now, the problem with my query is that I'm getting 1 as no of likes for every post.

Here's my query:

 SELECT x.count as likes, 
        a.id, 
        a.uid, 
        a.post, 
        a.created, 
        b.username,
        b.photo 
   FROM (
         SELECT count(*) AS count 
           FROM postlikes AS c
                JOIN posts AS d 
                     ON c.pid=d.id
         ) x 
             JOIN posts AS a
             JOIN user AS b 
                  ON a.uid=b.id 
  LIMIT 0,10

Answer 1

This is a good example of a case that requires GROUP BY.

select u.username
,      p.created
,      p.post
,      p.id   -- post_id
,      p.uid  -- user_id
,      count(pl.pid) likes
from   users u
join   posts p on u.id = p.uid
left join postlikes pl on pl.pid = p.id
group by u.username
,      p.created
,      p.post
,      p.id
,      p.uid

Answer 2

Bit longer but can be helpful

select u.username,p.created,p.post,p.id,p.uid,
(select count(*) from postlikes where postlikes.pid = p.id and postlikes.uid = u.id ) as likes
from   users u
join   posts p on u.id = p.uid
group by u.username ,p.created,p.post,p.id,p.uid