Not returning all results

Databases
1

I have three users: user 1 has two posts, user 2 has one post, user 3 has one post. No comments have been added.

Yet if I run the following query I only get one post for user with ID 1, and one post for user with ID 2. I get nothing for user with ID 3.

However, if I remove the COUNT from the comments table, all posts show up. Any ideas as to what I’m doing wrong?

SELECT 
     posts.id as post_id
    ,posts.text 
    ,posts.name
    ,posts.photo
    ,posts.hash as post_hash
    ,posts.date_added as post_date_added 
    ,users.id as usr_id
    ,users.uname 
    ,users.avatar 
    ,users.sex 
    ,users.hash as usr_hash
    ,COUNT(comments.id) AS comment_count
FROM 
    followers
        LEFT OUTER JOIN users 
            ON users.id = followers.following
        LEFT OUTER JOIN posts 
            ON posts.usr_id = followers.following 
        LEFT OUTER JOIN comments 
            ON comments.post_id = posts.id
WHERE 
    followers.user=1
UNION  
SELECT 
     posts.id as post_id
    ,posts.text 
    ,posts.name
    ,posts.photo
    ,posts.hash as post_hash
    ,posts.date_added as post_date_added 
    ,users.id as usr_id
    ,users.uname 
    ,users.avatar 
    ,users.sex 
    ,users.hash as usr_hash
    ,COUNT(comments.id) AS comment_count
FROM 
    followers
        LEFT OUTER JOIN users 
            ON users.id=1
        LEFT OUTER JOIN posts 
            ON posts.usr_id=1
        LEFT OUTER JOIN comments 
            ON comments.post_id = posts.id
ORDER BY 
    post_date_added 
LIMIT 10
2

you forgot your GROUP BY clause

3

GROUP BY posts.id still omits user 3

4

can i see the query that you tried please

5

Thanks, Rudy. I got it sorted out. I was missing the GROUP BY in the first subselect :slight_smile:

6

I spoke too soon. The second subselect is returning the wrong number of comments for user 1

SELECT 
     posts.id as post_id
    ,posts.text 
    ,posts.name
    ,posts.photo
    ,posts.hash as post_hash
    ,posts.date_added as post_date_added 
    ,users.id as usr_id
    ,users.uname 
    ,users.avatar 
    ,users.sex 
    ,users.hash as usr_hash
    ,COUNT(comments.id) AS comment_count
FROM 
    followers
        LEFT OUTER JOIN users 
            ON users.id = followers.following
        LEFT OUTER JOIN posts 
            ON posts.usr_id = followers.following 
        LEFT OUTER JOIN comments 
            ON comments.post_id = posts.id
WHERE 
    followers.user=1
GROUP BY 
    posts.id 
UNION 
SELECT 
     posts.id as post_id
    ,posts.text 
    ,posts.name
    ,posts.photo
    ,posts.hash as post_hash
    ,posts.date_added as post_date_added 
    ,users.id as usr_id
    ,users.uname 
    ,users.avatar 
    ,users.sex 
    ,users.hash as usr_hash
    ,COUNT(comments.id) AS comment_count
FROM 
    followers
        LEFT OUTER JOIN users 
            ON users.id=1
        LEFT OUTER JOIN posts 
            ON posts.usr_id=1
        LEFT OUTER JOIN comments 
            ON comments.post_id = posts.id
GROUP BY 
    posts.id 
ORDER BY 
    post_id DESC 
LIMIT 10
7

here’s a general strategy that i use to debug a query

you have a COUNT problem, so let’s see why it doesn’t work the way you expect

remove the COUNT, remove the GROUP BY, and do a – gasp! – select star

do it for each subselect separately

use judicious ORDER BY clauses to sequence the data the way you expect the values to be counted, then count the values yourself

if they don’t add up, you’re closer – i.e. at the join level – to the problem than you are by looking at the counts

8

Good tips, thanks. I was able to refine my queries by breaking them up like that and going over and over this until my eyes bled. :slight_smile:

Still having a problem with COUNT. The two queries produce the correct results when run independently AND when concatenated with a UNION. However, when I thrown a COUNT in for the comments table all hell breaks loose again and I get weird results.

SELECT 
     posts.id as post_id
    ,posts.text 
    ,posts.name
    ,posts.photo
    ,posts.hash as post_hash
    ,posts.date_added as post_date_added 
    ,users.id as usr_id
    ,users.uname 
    ,users.avatar 
    ,users.sex 
    ,users.hash as usr_hash
    ,comments.comment
FROM 
    followers
        LEFT OUTER JOIN users 
            ON users.id = followers.following
        LEFT OUTER JOIN posts 
            ON posts.usr_id = followers.following 
        LEFT OUTER JOIN comments 
            ON comments.post_id = posts.id 
WHERE 
    followers.user=1    



SELECT 
     posts.id as post_id
    ,posts.text 
    ,posts.name
    ,posts.photo
    ,posts.hash as post_hash
    ,posts.date_added as post_date_added 
    ,users.id as usr_id
    ,users.uname 
    ,users.avatar 
    ,users.sex 
    ,users.hash as usr_hash
    ,comments.comment
FROM 
    posts
        LEFT OUTER JOIN users 
            ON users.id=1
        LEFT OUTER JOIN comments 
            ON comments.post_id = posts.id 
WHERE 
    posts.usr_id=1
ORDER BY 
    post_id DESC
9

Looks like changing

COUNT(comments.id) as comment_count

to

COUNT(comments.comment) as comment_count

fixed it. :slight_smile: