Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2006
    Posts
    1

    Question Unanswered: Double left join from same table issue

    Hi I am looking for some help with a MySQL problem that I have had for some time now.

    I have two tables, one (articles) containing the columns id and title, and the other (comments) containing the columns id, name, status, and parent. I want to join the comments table onto the articles table twice so that I can have a column containing the total number of active comments (status=1) for each article, and one containing the total number of inactive comments (status=0). I have had little success in getting the columns to form correctly. I am attempting this problem with the following query:-

    SELECT articles.title, COUNT(comments1.id) AS active, COUNT(comments2.id) AS inactive
    FROM articles
    LEFT JOIN comments AS comments1 ON (articles.id=comments1.parent AND comments1.status=1)
    LEFT JOIN comments AS comments2 ON (articles.id=comments2.parent AND comments2.status=0)
    GROUP BY articles.id;

    Unfortunately active and inactive produce the same, wrong number per article.

    BTW I am restricted to using MySQL 4.0 so cannot get round this problem using subqueries in the SELECT.

    If anyone can help I will be very grateful.

    Monkey Ninja

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT articles.title
         , sum(case when comments.status=1
                    then 1 else 0 end) AS active
         , sum(case when comments.status=0
                    then 1 else 0 end) AS inactive
      FROM articles
    LEFT 
      JOIN comments AS 
        ON articles.id = comments.parent 
    GROUP 
        BY articles.title
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    Quote Originally Posted by r937
    Code:
    SELECT articles.title
         , sum(case when comments.status=1
                    then 1 else 0 end) AS active
         , sum(case when comments.status=0
                    then 1 else 0 end) AS inactive
      FROM articles
    LEFT 
      JOIN comments AS 
        ON articles.id = comments.parent 
    GROUP 
        BY articles.title
    This maybe simpler

    Code:
    SELECT
      articles.title,
      SUM(IF(comments.status = 1, 1, 0) AS "active",
      SUM(IF(comments.status = 0, 1, 0) AS "inactive",
    FROM articles
      LEFT JOIN comments AS
        ON (articles.id = comments.parent)
    GROUP 
      BY articles.title
    isn't it ?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, it's not simpler

    you have substituted a non-standard function (IF) for my standard sql CASE expression

    you have needlessly double-quoted the column aliases

    you have needlessly parenthesized the join condition

    unfortunately, you copied over my syntax error (keyword AS without table alias name), but you also introduced a new syntax error, the dangling comma

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

  5. #5
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    ... you have substituted a non-standard function (IF) for my standard sql CASE expression ...
    Ok sorry I didn't konw that, good to know.

    ... you have needlessly double-quoted the column aliases ...
    For me it's not needless because the alias can potentially contain spaces (that I never do but so anybody else can edit my query later)

    ... you have needlessly parenthesized the join condition ...
    For me it's not needless because the JOIN condition(s) can be multiple (and anyway it's the norm that I use for conditions in any langage)

    ... the dangling comma
    indeed.

    Finally if we want to be purists maybe we should do that
    Code:
    SELECT
      articles.title
      ,
      sum(
        case
          when comments.status=1
            then 1
          else 0
        end) AS active
      ,
      sum(
        case
          when comments.status=0
            then 1
          else 0
        end) AS inactive
    FROM
      articles
      LEFT JOIN comments
        ON articles.id = comments.parent 
    GROUP 
        BY articles.title

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