#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2010
    Posts
    195
    Rep Power
    45

    Count number of results across multiple tables


    Today I built a nested commenting feature for our site using two mysql tables. The client wants me to be able to provide them with a count of total comments on a particular project. This includes both primary comments (which are posted directly to the project), and then the nested comments, which are basically people commenting on other people's comments.

    The project_ID is only stored in the primary comments table. The subcomments table uses the comment_ID from the comments table in order to tie that subcomment to the primary comment.

    I wrote the following JOIN query in an effort to get this count:
    PHP Code:
    $comment_count 
        
    mysql_num_rows(
            
    mysql_query("
                SELECT 
                    comments.comment_ID,
                    comments.comment_text AS primary_comment,
                    subcomments.subcomment_ID,
                    subcomments.comment_text AS nested_comment
                FROM comments
                LEFT JOIN subcomments 
                ON comments.comment_ID=subcomments.comment_ID
                WHERE comments.project_ID='
    $project_ID'
            "
    )
        ); 
    The comments table contains 2 comments for this project_ID. The subcomments table contains 2 subcomments for the first comment_ID. Thus, $comment_count should be 4. But because I am using the JOIN, it's not counting the primary comment that has the subcomments attached to it.

    Any thoughts on how I can properly count the total number of comments across both tables for this particular project?
  2. #2
  3. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7171
    What you need is a full outer join, but since MySQL doesn't support those you'll need to count the two tables separately.
    Code:
    SELECT 
    	(SELECT COUNT(1) FROM comments WHERE comments.project_ID = '{$project_ID}')
    	+
    	(SELECT COUNT(1) FROM subcomments INNER JOIN comments ON comments.comment_ID = subcomments.comment_ID AND comments.project_ID = '{$project_ID}')
    	AS number;

    Comments on this post

    • Jyncka agrees : Drives me crazy that MySQL can't do a full outer join.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2010
    Posts
    195
    Rep Power
    45
    Well, boo on MySQL but hooray for E-Oreo. Works like a charm, thank you as usual.

IMN logo majestic logo threadwatch logo seochat tools logo