CODEWITHSUNDEEP
mysql
Francis G
Francis G

Reputation: 1088

MySQL count() with LEFT JOIN

Many question has been asked with the same problem. But hear me out first. I have two tables joining using LEFT JOIN, the first table is the user table and the second table is just a referential table division table

user table

 user_id |  name   |   division_id
 0       |  ABC    |     0
 1       |  DEF    |     1
 2       |  GHI    |     1

division table

id  |  name   | protect class
1   |  div_1  |      1

my original query

SELECT count(usr.user_id) 
FROM user as usr 
    LEFT JOIN division as dv 
        ON dv.id = usr.division_id
WHERE 
    dv.protect_class != '1'

but when I want to select user without a division 

SELECT count(usr.user_id) 
    FROM m_users as usr 
        LEFT JOIN m_division as dv 
            ON dv.id = usr.division_id OR  usr.division_id = '0'
    WHERE 
         dv.protect_class != '1'

Things got a little weird, the user without division_id or equals to 0 get counted multiple times base on how many division entry a got.

I also tried adding distinct

SELECT distinct count(usr.user_id)
    FROM m_users as usr 
        LEFT JOIN m_division as dv 
            ON dv.id = usr.division_id OR usr.division_id = '0'
    WHERE 
        dv.protect_class != '1'

But still no luck for me. And when I query this

SELECT distinct usr.*
    FROM m_users as usr 
        LEFT JOIN m_division as dv 
            ON dv.id = usr.division_id OR usr.division_id = '0'
    WHERE 
        dv.protect_class != '1'

And count it physically (by hand) the count is correct, then if I apply the count() function the count is wrong. So I just want to ask for advise on, How can I count the entry without counting the duplicate in the my LEFT JOIN?

Upvotes: 2

Views: 665

Answers (2)

j-shan huang
j-shan huang

Reputation: 130

Join with 'OR' it cause multiple join when match condition

SELECT count(usr.user_id) 
    FROM m_users as usr 
    LEFT JOIN m_division as dv 
         ON dv.id = usr.division_id
    WHERE 
         dv.protect_class != '1'
         and (usr.division_id = '0' OR usr.division_id IS NULL)

Upvotes: 0

Fahmi
Fahmi

Reputation: 37483

you can try below - count(distinct usr.user_id)

SELECT count(distinct  usr.user_id)
    FROM m_users as usr 
        LEFT JOIN m_division as dv 
            ON dv.id = usr.division_id OR usr.division_id = '0'
    WHERE 
        dv.protect_class != '1'

Upvotes: 1

Related Questions