CODEWITHSUNDEEP
mysqldatabase
Bhavin
Bhavin

Reputation: 2158

Join in MYSQL (Count from One table and list from one table)

I have two table,

In User Table there are following columns, 

userid , email , phone_number

In Report User Table there are following columns,

id , userid , report_user_id , reason

So, I want to fetch the list of user with report count if count is zero then it must be with zero.

User Table, 

userid | email         | phone_number
1      | [email protected] | 12312312
2      | [email protected] | 112312312
3      | [email protected] | 112312312

Report User Table,

id     |userid | report_user_id | phone_number
1      | 2     |   3            | 12312312
2      | 3     |   2            | 112312312
3      | 1     |   2            | 112312312

Expected OutPut, 

userid | email         | phone_number | report_count
1      | [email protected] | 12312312     | 0
2      | [email protected] | 112312312   | 2
3      | [email protected] | 112312312   | 1

Here userid = 1 has zero report count so it must be zero (because in report_user_id column there is no entry of 1) , userid = 2 has 2 report count so it must be 2 , and userid = 3 has 1 report count so it must be zero.

I have tried this query but I'm not able to get the expected result,

SELECT count(LRU.report_user_id) as report_count FROM `lickr_report_user` as LRU LEFT JOIN lickr_users as LU ON LU.userid = LRU.report_user_id GROUP BY LU.userid

Upvotes: 0

Views: 34

Answers (1)

kiks73
kiks73

Reputation: 3758

I think yo reversed the user and report table in the join statement:

You should also add an IFNULL substitute 0 to null in COUNT function.

SELECT LU.userid, 
       LU.email, 
       LU.phone_number, 
       COUNT(IFNULL(LRU.report_user_id),0) as report_count 
FROM lickr_users as LU
LEFT JOIN `lickr_report_user` as LRU ON LU.userid = LRU.report_user_id 
GROUP BY LU.userid, LU.email, LU.phone_number

Upvotes: 1

Related Questions