By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,575 Members | 1,948 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,575 IT Pros & Developers. It's quick & easy.

Getting unmapped records with mapping table query

Telinstryata
P: 11
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

Expand|Select|Wrap|Line Numbers
  1.     SELECT
  2.         RbId,
  3.                 RbDescription
  4.         COUNT(RbId) AS TheCount
  5.     FROM ReferredBy AS R
  6.     INNER JOIN VisitRefByMap AS M
  7.         ON M.RbId = R.RbId
  8.     LEFT OUTER JOIN Visits AS V
  9.         ON M.VisitId = V.VisitId
  10.         WHERE V.AccountId = 2
  11.     GROUP BY
  12.         RbId,
  13.                 RbDescription
  14.  
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...

Expand|Select|Wrap|Line Numbers
  1. RbId   RbDescription_E TheCount
  2. 1      Parent           3
  3. 4      Other            1
  4.  
I want to also return all of the ReferredBy records that have 0 mappings so that my output is as follows...

Expand|Select|Wrap|Line Numbers
  1. RbId   RbDescription_E TheCount
  2. 1      Parent          3
  3. 2      Child           0        
  4. 3      Friend          0
  5. 4      Other           1
  6.  
Can anyone help me with the syntax to produce the results I'm looking for?
Feb 20 '09 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.