Hey everyone!
I’ve got a blog community site I’m building (I know, like the internet needs another one right?).
Anyway, what I’m trying to do is to select the 10 most recently posted entries, but want to limit it to one per user, and a maximum of 10.
To display the details about the user, I do a join on the users table to pull in their info. I’m also joining the count of the number of comments that particular entry has.
Here is my query which is partially working. It’s bring back more or less expected results, except that there are multiple entries from the same user.
[php]SELECT DISTINCT t1.uid, LEFT(t1.body, 420) AS body, t1.title,
t1.datePosted, t1.id, t1.type, t1.showOnHome, t1.privacyType,
t2.userName, COUNT(t3.id) AS numComments
FROM entries AS t1
JOIN users AS t2 ON t2.uid = t1.uid
JOIN comments AS t3 ON t3.eid = t1.id
WHERE t1.privacyType >= 3 AND t1.showOnHome = 1
GROUP BY datePosted
ORDER BY datePosted
DESC
LIMIT 10[/php]
I’ve tried a few different variations on this, and this is the closest I’ve gotten so far.
Any help is appreciated! Thanks!