can I solve this query by using self join?

djuritz · #1 (**permalink**) 04-27-07, 18:25

Hi, I've this table in MySQL 4.0
It's from a website where you can post secrets and users can post comments to those secrets.
Columns are:
ID, post_date, content, comment_for

If post is new thread, comment_for will be null.
If post an answer to an existing secret, comment_for will be the ID of the original post.
ie:
Original post: ID = 100, DATE=2007-04-26, COMMENT_FOR=NULL, content=xxxxx
Reply: ID=999, DATE=2007-04-27 COMMENT_FOR=100, content=xxxxx

The point is that I need to know the most popular secrets (posts), and for doing it, I need to known the secrets with more answers.

How can I do that?
I known I have to use GROUP BY, but should I use a self referenced join or something like that?

Thanks in advance.
Diego.-

aschk · #2 (**permalink**) 04-30-07, 04:45

Using the following insert :

Code:

INSERT INTO secrets(`Date`,Comment_For,Content)
VALUES
(NOW(),NULL,'Some content'),(NOW(),NULL,'More Content'),
(NOW(),NULL,'Some content'),(NOW(),NULL,'More Content'),
(NOW(),3,'Some content'),(NOW(),3,'More Content'),
(NOW(),2,'Some content'),(NOW(),1,'More Content');

You can do the following to get each thread + the number of sub threads it contains:

Code:

SELECT *,(SELECT COUNT(*) FROM secrets WHERE s1.ID=Comment_For)as num_threads FROM secrets s1;

The answer I have given above is assuming that there is only one level of threading. i.e. master -> thread and not master -> thread -> thread. So you can't have a thread of a thread of a thread.

r937 · #3 (**permalink**) 04-30-07, 06:02

aschk, your solution involves a subquery, and subqueries are not supported in mysql 4.0

see also can I solve this query by using self join?

aschk · #4 (**permalink**) 04-30-07, 06:07

Ah yes, darn it. Why can't people just use 4.1 ...!

r937 · #5 (**permalink**) 04-30-07, 07:08

Code:

select t1.id
     , t1.post_date
     , count(t2.comment_for) as comments
  from secrets as t1
left outer
  join secrets as t2
    on t2.id = t1.id
group
    by t1.id
     , t1.post_date
order
    by t2.comments desc limit 10

Internet Services

Web Development

Digital Marketing

Consumer Tech