Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2005
    Posts
    67

    can I solve this query by using self join?

    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.-

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2005
    Posts
    67
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Hint: Upgrade...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •