CODEWITHSUNDEEP
mysqlleft-join
Bhuvnesh Gupta
Bhuvnesh Gupta

Reputation: 45

fetch all users and their number of records in another table

I have two tables users (user_id, name) and license (license_id,user_id)

I want to get all users with their number of records in license table. Question seems to be simple but I am not able to figure out its query.

I tried like 

SELECT * FROM `users` a left join license b on a.user_id=b.user_id

but this is giving me all rows of a and matched rows of b table while I want all rows of a and one another column with number of rows matched in b.

Please help me how to get this.

Upvotes: 1

Views: 509

Answers (3)

Piyush Gupta
Piyush Gupta

Reputation: 2179

Try this Query:

SELECT a.user_id, a.name, COUNT(b.user_id)
  FROM `users` a 
  LEFT JOIN license b ON a.user_id=b.user_id
  GROUP BY user_id, NAME;

Output: SEE DEMO HERE

Upvotes: 1

zakhefron
zakhefron

Reputation: 1443

SELECT 
  U.user_id, U.name, COUNT(L.license_id) as licenseCount
FROM `users` U
LEFT JOIN license L ON U.user_id=L.user_id
GROUP BY U.user_id;

Refer Sql Fiddle

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

If you want the number of matching user you should use count and group by

SELECT user_id, name, count(*)
FROM `users` a 
INNER join license b on a.user_id=b.user_id
Group by user_id, name

of you want only the count of matching 

select count(*) 
FROM `users` a 
INNER join license b on a.user_id=b.user_id

This instead return the count of number in license 

  select count(*) 
  FROM `users` a 
  LEFT join license b on a.user_id=b.user_id

and then you can obtain the same number simply using 

  select count(*) 
  from  license

otherwise if you want then number with alto the user don't matc then try 

SELECT user_id, name, ifnull(count(*), 0)
FROM `users` a 
LEFT join license b on a.user_id=b.user_id
Group by user_id, name

Upvotes: 2

Related Questions