SQL statement that Subs 0 without any results

Question

I am having a problem getting the results that I want to get from my SQL statement. I know that I probably missed something simple, but I just don't see it.

Here are my tables.

  Table: Users (RoleID is linked to ID in Roles Table)
 ID , FirstName , LastName , RoleID 
 1, Matt, Ryan, 1
 2, Chipper, Jones, 1
 3, Julio, Jones, 2
 4, Jason, Bourn, 3

 Table: Roles
 ID , Name
 1, Field Rep
 2, Tech
 3, admin

 Table: FRrequests (UserID is linked to ID in Users table) 
 ID , UserID , Status
 1, 1, Open
 2, 1, Submitted
 3, 1, Delayed
 4, 1, Complete

What I want is an SQL statement that shows me the number of all Sent and Pending requests for all fields. The following is an example of the desired results.

  Name count
 Chipper Jones 0
 Matt Ryan 2

Here is the expression that I still have, and the results that he gives me.

 SELECT Users.FirstName + '' + Users.LastName AS Name, COUNT (FRrequests.ID) AS 'Open Requests' FROM Users INNER JOIN Roles ON Users.RoleID = Roles.ID LEFT OUTER JOIN FRrequests ON Users.ID = FRrequests.UserID WHERE (Roles.Name = N'Field Rep ') AND (FRrequests.Status =' Submitted 'OR FRrequests.Status =' Delayed ') GROUP BY Users.FirstName, Users.LastName Name Count Matt Ryan 2 

I know that the AND part (FRrequests.Status = 'Submitted "or FRrequests.Status =' Delayed ') is what violates it. If I run it without this in the application, I will get all the users, but it will calculate that the whole status is not just sent and delayed. I just can't figure out what I'm missing to get this to work. Any help would be greatly appreciated.

Answer 1

Are you really close, try the following:

SELECT U.FirstName + ' ' + U.LastName AS Name, COUNT(F.ID) AS 'Open Requests' FROM Users U INNER JOIN Roles R ON U.RoleID = R.ID LEFT JOIN ( SELECT * FROM FRrequests WHERE Status IN ('Submitted','Delayed')) F ON U.ID = F.UserID WHERE R.Name = N'Field Rep' GROUP BY U.FirstName, U.LastName 

Answer 2

You need to have a row for each field if you want to include Rep fields that have no queries. Therefore, you need to use the left outer join on the following lines:

 SELECT u.FirstName || ' ' || u.LastName as Name , COALESCE(frr.Counter,0) as Count FROM Users u LEFT OUTER JOIN ( SELECT UserID ,count(*) as Counter FROM FRrequests WHERE Status IN ('Submitted', 'Delayed') GROUP BY UserID ) frr ON frr.UserID = u.ID , Roles r WHERE u.ID = f.UserID AND u.RoleID = r.ID AND r.Name = 'Field Rep' ; 

A left outer join will provide a join only where a match occurs, so it does not remove rows from a naturally joined set. In this case, you will receive a list of all users who are fields, together with their FRRquests account with the status "Submitted" or "Pending", if any.

COALESCE is used (at least in Postgresql) to check the null value, and then force it to the provided value in this case 0. Therefore, if the value is NULL (because, for example, there was no "counter", the value returned from left outer join), it is replaced with 0.