CODEWITHSUNDEEP
phpmysqlpdo
Tomek Buszewski
Tomek Buszewski

Reputation: 7935

MySQL count should return 0, returns nothing

I have this MySQL query, seems pretty easy:

SELECT users.nick, users.id, COUNT(pm.id) AS pms 
FROM users LEFT JOIN pm ON users.id = pm.touser  
WHERE users.id = :id AND pm.read = 0
GROUP BY users.id

What I want to do here is simply count all the PMs that aren't read (doesn't have "1" in "read" col). If there are any, query works fine, but when there isn't any row meeting that condition, it returns nothing.

I spent the last hour looking for solution, but it seems really odd, that it works this way. It should only print "0" while echoing "pms".

Upvotes: 2

Views: 468

Answers (4)

Dimitri
Dimitri

Reputation: 121

Simply:

SELECT users.nick, users.id, COUNT(pm.id) AS pms 
FROM users LEFT JOIN pm ON (users.id = pm.touser AND pm.read = 0)
WHERE users.id = :id
GROUP BY users.id;

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425411

SELECT  users.nick, users.id, COUNT(pm.id) AS pms 
FROM    users
LEFT JOIN
        pm
ON      (pm.touser, pm.read) = (users.id, 0)
WHERE   users.id = :id
GROUP BY
        users.id

pm.read = 0 should go to the ON clause.

WHERE clause filters out all NULL values resulting from the pm miss.

Upvotes: 0

Paul Grimshaw
Paul Grimshaw

Reputation: 21034

Try reversing the table order:

SELECT users.nick, users.id, COUNT(pm.id) AS pms 
FROM pm 
LEFT JOIN users 
ON users.id = pm.touser  
WHERE users.id = :id AND pm.read = 0
GROUP BY users.id

Upvotes: 0

user1432124
user1432124

Reputation:

Try this

SELECT nick,id, COUNT(id) AS pms 
FROM users LEFT JOIN (Select touser as id from pm where read =0) as pms1  
WHERE id = :id GROUP BY id

Upvotes: 0

Related Questions