How do I get an original row in a SQL join from disappearing?

Question

In this query:

SELECT COUNT(*) AS UserCount, Company.* FROM Company
LEFT JOIN User
ON User.CompanyId = Company.Id
WHERE Company.CanAccessSystem= true
AND(User.CanAccessSystem IS null OR User.CanAccessSystem = true)
GROUP BY Company.Id

I want to query a list of companies that can access a particular system as well as the number of users who can access the system inside the company.

This query works for all cases except for one very important one. If a company can access a the system but none of the users can, the Company disappears completely from the query (i.e.: Users.CanAccessSystem = false). In that case, I just want the UserCount = 0.

Example From Companies that Can Access the System:

Users   Company Name
1       WidgetWorks
3       WidgetCompany
0       WidgesRUs

This system is on MySQL

Query Edit: Fixed a Typo "ON User.CompanyId = Company.Id"

Answer 1

You should be counting the number of users for whom User.CanAccessSystem is true. Think of something like

 count(case when User.CanAccessSystem then true end)

The case expression will return NULL in case User.CanAccessSystem is false (default), and count(expr) counts the number of items for which the expression is not null.

So... This?

SELECT COUNT(case when User.CanAccessSystem then true end) AS UserCount, Company.*
FROM Company LEFT JOIN User
ON Company.id = user.companyId   -- I had to guess, this seems to be missing in your query
WHERE Company.CanAccessSystem= true
GROUP BY Company.Id

p.s. I used LEFT JOIN instead of INNER JOIN in order not to exclude companies without users.

Answer 2

Here's a little trick to do what you wish, taking advantage of bools are ones or zeroes you can sum(canaccess) to get the amount of users who can access the system. To avoid problems with NULLs, there's the COALESCE that'll transform NULLs into 1s (ie, can access system)

mysql> select * from companies;
+------+------------+-----------+
| name | company_id | canaccess |
+------+------------+-----------+
| foo  |          1 |         1 |
| bar  |          2 |         1 |
| baz  |          3 |         1 |
| quux |          4 |         1 |
+------+------------+-----------+
4 rows in set (0.00 sec)

mysql> select * from users;
+---------+------------+-----------+
| user_id | company_id | canaccess |
+---------+------------+-----------+
|       1 |          1 |         1 |
|       2 |          1 |         0 |
|       3 |          1 |         1 |
|       4 |          2 |      NULL |
|       5 |          2 |         1 |
|       6 |          2 |         0 |
|       7 |          3 |         1 |
|       8 |          3 |         0 |
|       9 |          4 |         0 |
+---------+------------+-----------+
9 rows in set (0.00 sec)

mysql> select company_id, sum(canaccess) from 
(select users.user_id, coalesce(users.canaccess,1) as canaccess, 
users.company_id, companies.name from companies join users 
on (users.company_id = companies.company_id) where companies.canaccess = 1 ) foo 
group by company_id;
+------------+----------------+
| company_id | sum(canaccess) |
+------------+----------------+
|          1 |              2 |
|          2 |              2 |
|          3 |              1 |
|          4 |              0 |
+------------+----------------+
4 rows in set (0.00 sec)

Answer 3

The reason that your result doesn't work is because you don't have any join clause.

SELECT IFNULL(COUNT(User.Id), 0) AS UserCount, Company.* 
FROM Company
LEFT JOIN User ON User.CompanyId = Company.Id AND User.CanAccessSystem = true
WHERE Company.CanAccessSystem = true
GROUP BY Company.Id

That should work. The point with a left join is that the master table-entries should always appear, however left joined entries doesn't have to.

the IFNULL() is only for returning 0 since no appropriate users will render a NULL-value in this case. I'm not really sure how you handle boolean values in MySQL since it doesn't support it natively.

Answer 4

I think I'd start with a sub-query that generates the (disjoint) union of:

• A company ID and a (non-zero) count of the users that can access the system where at least one user can access the system
• A company ID and a zero count when no user can access the system

Assuming that User.CanAccessSystem IS NULL was an artefact needed to account for the LEFT JOIN, that leads to:

SELECT Company.ID, COUNT(*) AS UserCount
    FROM Company, User
    WHERE Company.ID = User.CompanyID
      AND User.CanAccessSystem = true
UNION
SELECT Company.ID, 0 AS UserCount
    FROM Company
    WHERE NOT EXISTS (SELECT * FROM User
                         WHERE Company.ID = User.CompanyID
                           AND User.CanAccessSystem = true)

You could filter both parts with 'AND Company.CanAccessSystem = true' and it might be beneficial if most companies cannot access the system -- or you can defer it until the final processing stage.

You then need to do a straight join of this result with Company, ensuring that the filter condition for the company being able to access the system is applied somewhere along the line.

Nominally, this leads to the following (untested) code:

SELECT UserCount, Company.*
    FROM Company JOIN
        (SELECT Company.ID AS ID, COUNT(*) AS UserCount
             FROM Company, User
             WHERE Company.ID = User.CompanyID
               AND User.CanAccessSystem = true
         UNION
         SELECT Company.ID AS ID, 0 AS UserCount
             FROM Company
             WHERE NOT EXISTS (SELECT * FROM User
                                  WHERE Company.ID = User.CompanyID
                                    AND User.CanAccessSystem = true)
        ) AS NumUsers
         ON Company.ID = NumUsers.ID
    WHERE Company.CanAccessSystem = true

Answer 5

I have no MySQL installed... Tried this on MSSQL and i believe, this will handle your scenario, with a little modification to be MySQL valid...

SELECT c.CompanyID, c.CompanyName, SUM(CASE u.CanAccessSystem WHEN 1 then 1 else 0 end) 
FROM Company c LEFT OUTER JOIN
    [User] u
ON u.CompanyID = c.CompanyID
WHERE c.CanAccessSystem = 1
GROUP BY c.CompanyID, c.CompanyName