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

    Unanswered: can I solve this query by using self join?

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

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    aschk, your solution involves a subquery, and subqueries are not supported in mysql 4.0

    see also http://www.dbforums.com/showthread.php?t=1617695
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Ah yes, darn it. Why can't people just use 4.1 ...!

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

Posting Permissions

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