CODEWITHSUNDEEP
sqlsqliterelational-database
Shub
Shub

Reputation: 2704

include users having no records

In my relational Database and I have 2 tables:

user

id | userName | email | col1 | col2

log

ID | user_id | cl1 | cl2 | cl3

And I want in my admin section to view all the users along with the count of their records. I am using SQLite3 as my DB

SELECT user_id,username,email,count(link)'count'
    FROM log,user
    where user_id=user.id group by user_id;

it works fine but doesn't include users having no record in log table please help how can I make it include all the users and show the count value as 0.

Upvotes: 0

Views: 100

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93724

Use LEFT OUTER JOIN to include all the rows from user instead of old style of comma separated INNER JOIN

SELECT user.id,username,email,count(link)'count' 
FROM user 
LEFT OUTER JOIN Log
ON Log.user_id=user.id 
group by user.id;

Upvotes: 1

Related Questions