MySQL count() with LEFT JOIN

Question

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?

yes a simple data set for check the behavior you obtain for the severl queries — ScaisEdge
– ScaisEdge, Commented Apr 25, 2019 at 9:45

Fahmi · Accepted Answer · 2019-04-25 09:42:48Z

1

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'

answered Apr 25, 2019 at 9:42

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Francis G Over a year ago

Wow that was fast, thanks it works, I didn't know that distinct can be use as param in count()

j-shan huang · Accepted Answer · 2019-04-25 09:46:31Z

0

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)

answered Apr 25, 2019 at 9:46

j-shan huang

1301 silver badge3 bronze badges

Collectives™ on Stack Overflow

MySQL count() with LEFT JOIN

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related