Double-joined, grouped select statement... 1

[theEclipse]

I am going to try and explain this without dumping my whole database into this little box and hoping you all read it ;-). The design should be normal enough. If needed, I can post the definitions.

I am setting up a multi-user CMS:
a table for content, a table for users, and a table for comments on content. Special concern: both Content and comments tables have a [tt]deleted[/tt] column for the purpose of data retention

For a site-map page, I am listing every content entry with its author and number of comments, among other things.:

SELECT
	COUNT(comments.id) AS commentCount,
	content.id,
	content.title,
	UNIX_TIMESTAMP(content.date) as date,
	content.text,
	users.name AS author
FROM content
LEFT JOIN comments ON comments.contentid=content.uid
JOIN users ON content.author=users.id
WHERE(content.deleted=false)
GROUP BY content.id
ORDER BY content.date DESC;
END;

I have tried all manner of where clauses and groupings in order to get the [tt]commentCount[/tt] field to only count the number of comments which are not deleted.

The solution is probably right in front of me...but I have been staring at this problem for about two weeks and made no progress.

Any ideas?
Thanks for reading.

Robert Carpenter
Remember....eternity is much longer than this ~80 years we will spend roaming this earth.
ô¿ô

http://www.robertcarpenter.net

 

[r937]

SELECT x.commentCount
     , content.id
     , content.title
     , UNIX_TIMESTAMP(content.date) as date
     , content.text
     , users.name AS author
  FROM content
INNER
  JOIN users 
    ON users.id = content.author
LEFT OUTER
  JOIN ( SELECT contentid
              , COUNT(*) AS commentCount
           FROM comments 
          WHERE deleted = false
         GROUP
             BY contentid ) AS x
    ON x.contentid = [red]content.id[/red]
 WHERE content.deleted = false
ORDER 
    BY content.date DESC

r937.com | rudy.ca

 

[theEclipse]

r937 - thanks!

I was hoping to not have to go the sub-select route. Is there a way around that? Until I tried yours, I wasn't sure that PHP allowed subselects.

Otherwise, it seems to work just fine.

I am curious about your sql syntax though...is that a standard way of laying out SQL?

Robert Carpenter
Remember....eternity is much longer than this ~80 years we will spend roaming this earth.
ô¿ô

http://www.robertcarpenter.net

 

[r937]

what's wrong with subselects? why don't you like them?

my syntax is pretty much standard SQL

the way the query is laid out, though -- that's my own particular style

easy to read, isn't it ;-)

r937.com | rudy.ca

 

[theEclipse]

About subselects: honestly...I thought that engines like PHP didn't support them. Plus, if I remember correctly, my database professor in college had a philosophy something like "Sub-selects are evil. Use them only when there is no other solution. Even then, try not to use them." But that could just be my bad memory too. :-D

Right, by syntax, I meant...uh...formatting. Well, its different, but I like it. ;-)

Robert Carpenter
Remember....eternity is much longer than this ~80 years we will spend roaming this earth.
ô¿ô

http://www.robertcarpenter.net

 

[r937]

subselects are evil??? your professor must have been on mysql 3.23

by the way, php has nothing to do with subselects

r937.com | rudy.ca