Only LIMIT rows with specific number

Question

Sorry for the title, As I'm not that experienced I had a real hard time trying to come up with a proper title.

So I have this query to fetch all the comments of a specific topic. Now for my paging I want to limit it down to 20 rows, but ONLY for posts with pid (parent_id) zero.

SELECT *, c.id AS cid, u.id AS userid 
FROM comments c 
INNER JOIN users u ON u.id = c.uid 
WHERE c.tid = :id ORDER BY c.posted ASC LIMIT 20

How do I go about limiting the posts by 20 WHERE pid = 0 without removing every row with pid = 1?

If I add:

 WHERE c.tid = :id AND c.pid = 0

I'll just get all comments without a pid.

So, for example, it shows 100 comments, but only 20 that has pid = 0 in the database. That's what I want.

Answer 1

Maybe you could try with:

WHERE c.tid = :id AND c.pid IN(0,1)

or

WHERE c.tid = :id AND c.pid <=1

but I am not sure what you want to get. Maybe an UNION is more appropriate.

-- only 20 with c.pid = 0
(SELECT *, c.id AS cid, u.id AS userid 
FROM comments c INNER JOIN users u ON u.id = c.uid 
WHERE c.tid = :id AND c.pid = 0 
LIMIT 20)
UNION
(SELECT *, c.id AS cid, u.id AS userid 
FROM comments c INNER JOIN users u ON u.id = c.uid 
WHERE c.tid = :id AND c.pid = 1)
ORDER BY c.posted ASC