Bhuvnesh Gupta Bhuvnesh Gupta - 5 months ago 9
SQL Question

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.

Answer

Try this Query:

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