1

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.