How do I get the original string in a SQL join from fading out?
In this request:
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 for a list of companies that can access a particular system, as well as the number of users that can access the system within the company.
This query works for all but one very important case. If the company can access the system, but none of the users can, the Company disappears completely from the request (i.e.: Users.CanAccessSystem = false). In this case, I just want UserCount = 0.
An example of companies that can access the system:
Users Company Name
1 WidgetWorks
3 WidgetCompany
0 WidgesRUs
This system is on MySQL
Editing requests: Fixed Typo "ON User.CompanyId = Company.Id"
source to share
The reason your result isn't working is because you don't have a connection suggestion.
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
This should work. The point with left-join is that master-table-records should always be displayed, but left-joined records should not.
IFNULL () is only intended to return 0, since in that case the corresponding users will not display NULL. I'm not sure how you handle boolean values in MySQL as it doesn't support it natively.
source to share
I think I'll start with a sub-query that generates a (non-overlapping) union:
- Company ID and counter (non-zero) of users who can access the system in which at least one user can access the system.
- Company ID and zero account if the user cannot access the system.
Assuming there User.CanAccessSystem IS NULL
was an artifact required to account for the LEFT JOIN, resulting in:
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 can filter out both parts with "AND Company.CanAccessSystem = true" and can be useful if most companies cannot access the system - or you can postpone it until the finalization stage.
Then you need to do a direct connection of this result to the Company, ensuring that the filtering condition for the company that has access to the system is applied somewhere along the line.
This nominally results in 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
source to share
You should count the number of users for which User.CanAccessSystem is true. Think of something like
count(case when User.CanAccessSystem then true end)
The case expression returns NULL if User.CanAccessSystem is false (the 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
ps I used LEFT JOIN instead of INNER JOIN so as not to exclude companies without users.
source to share
Here's a little trick to do what you want using bools are those or zeros, which you can sum (canaccess) to get the number of users that can access the system. To avoid NULL problems, there are COALESCEs which convert NULL to 1s (i.e. can access the 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)
source to share
I don't have MySQL installed ... Tried this in MSSQL and I trust this will handle your script, with a little change to be correct MySQL ...
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
source to share