0

Im doing the follwing, to create an user report

SELECT b.username, b.name, b.permissiontoedit, a.total, a.user
FROM (SELECT user, Count( * ) AS total
FROM products
GROUP BY user)a
JOIN user b ON a.user = b.username

This should give a table with the username, full name, permision (1/0) and the total of entries.

Sadly, the query does only list users, which made more 1 or more entries in the table products. But i want all users, and if the have not made any entries in products it should display 0 or nothing.

where do i made a mistake?

sgtBear
  • 61
  • 2
  • 10

1 Answers1

1

Using a LEFT JOIN you can get your result:

SELECT 
    u.username, u.name, u.permissiontoedit, COUNT(p.user) as total
FROM
    user u
LEFT JOIN
    products p
ON
    u.username = p.user

Note: COUNT(expression) counts only NOT NULL rows in contrast to COUNT(*) that counts every row.

VMai
  • 10,156
  • 9
  • 25
  • 34