Bhuvnesh Gupta Bhuvnesh Gupta - 1 year ago 57
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 Source

Try this Query:

SELECT a.user_id,, 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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download