MySQL Forums
Forum List  »  Newbie

Re: getting a count of records based on inner relationships...?
Posted by: Felix Geerinckx
Date: May 12, 2005 04:24PM

Brian Culler wrote:

> I am making a PHP-MySQL forum. I have a table "forum" that holds all the posts.
> In my main forum view where I list the last 10 threads, I want returned in each
> result set the ID, title, user, and the # of comments posted in that thread.

USE test;
DROP TABLE IF EXISTS forum;
CREATE TABLE forum (
id INT NOT NULL PRIMARY KEY,
user VARCHAR(20),
threadid INT
);
INSERT INTO forum (id, user, threadid) VALUES
(1, 'you', NULL),
(2, 'me', 1),
(3, 'you', 1),
(4, 'me', 1),
(5, 'someone else', NULL),
(6, 'me', NULL),
(7, 'someone else', 6);

SELECT
f1.id,
f1.user,
COUNT(f2.id) AS comments
FROM forum AS f1
LEFT JOIN forum AS f2 ON f2.threadid = f1.id
WHERE f1.threadid IS NULL
GROUP BY f1.id
ORDER BY f1.id DESC
LIMIT 0,10;

--
felix
Please use BBCode to format your messages in this forum.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.