I have 3 tables for visits. "Visits" holds the visit information, "ReferredBy" holds the different types of referred-by info, and VisitRefByMap maps the two together in a many-to-many relationship.
Visits: VisitId, AccountId, TypeOfVisitId...
ReferredBy: RbId, RbDescription
VisitRefByMap: VisitId, RbId
I am trying to get a count of how many of each type of "referred-by" are being used per account which I have accomplished but I want to also get back the values for referred by that are not being used, but with a 0 for the count.
This is what I have so far
-
SELECT
-
RbId,
-
RbDescription
-
COUNT(RbId) AS TheCount
-
FROM ReferredBy AS R
-
INNER JOIN VisitRefByMap AS M
-
ON M.RbId = R.RbId
-
LEFT OUTER JOIN Visits AS V
-
ON M.VisitId = V.VisitId
-
WHERE V.AccountId = 2
-
GROUP BY
-
RbId,
-
RbDescription
-
This returns the count of each that I need, but the rows in ReferredBy that do not have any mappings do not return. Currently this returns...
-
RbId RbDescription_E TheCount
-
1 Parent 3
-
4 Other 1
-
I want to also return all of the ReferredBy records that have 0 mappings so that my output is as follows...
-
RbId RbDescription_E TheCount
-
1 Parent 3
-
2 Child 0
-
3 Friend 0
-
4 Other 1
-
Can anyone help me with the syntax to produce the results I'm looking for?
