can I solve this query by using self join?

djuritz · #1 (**permalink**) 04-27-07, 13:01

Hi, I've this table in MySQL.
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.-

r937 · #2 (**permalink**) 04-27-07, 13:32

Code:

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

djuritz · #3 (**permalink**) 04-27-07, 13:45

Dear r937.

Thanks for your reply.
But I'm afraid MySQL 4.0 doesn't support on-line views as Oracle does.
Is there any other way?

Thanks!

Quote:

Originally Posted by r937

Code:

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

r937 · #4 (**permalink**) 04-27-07, 17:07

sorry, my answer was made on the assumption that the question was being asked by someone interested in Database Concepts & Design, not someone with an extremely antiquated version of a database system that at one point was unable to support the more complex but fully standard sql constructions

you should have posted in the mysql forum, and you should have mentioned your ancient version

blindman · #5 (**permalink**) 04-27-07, 18:43

Hint: Upgrade...

Internet Services

Web Development

Digital Marketing

Consumer Tech