Double left join from same table issue

monkey-ninja · #1 (**permalink**) 03-19-06, 13:59

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

r937 · #2 (**permalink**) 03-19-06, 20:44

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

gtk · #3 (**permalink**) 03-21-06, 08:41

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 ?

r937 · #4 (**permalink**) 03-21-06, 11:24

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

gtk · #5 (**permalink**) 03-21-06, 12:19

Quote:

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

Ok sorry I didn't konw that, good to know.

Quote:

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

Quote:

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

Quote:

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

r937 · #6 (**permalink**) 03-21-06, 12:51

cool

Internet Services

Web Development

Digital Marketing

Consumer Tech