Row Count from Table B for Results From Table A

Question

I am working on building a report that returns members from the MEMBER table that joined between a date range, the relationship (MEMBERS.relationship) column equals 'M' for member, and their membership type is one of THEN for each member record that matches this criteria, I would need to return the number of checkins they had for the period specified from the CHKINS table. there is one entry in the CHKINS table for each checkin and the 'ischeckin' field needs to be set to 'True' to be a valid checkin. In other words, John Doe checked into the facility 10 times for the period specified. I need one row returned with the fields specified plus the value '10'. What I have build now gives me ten separate rows.

SELECT        
    EMPLOYEES.lname, EMPLOYEES.fname, 
    MEMBERS.fname AS Expr1, MEMBERS.scancode, MEMBERS.lname AS Expr2, 
    MEMBERTYPES.description, 
    MEMBERS.phone1, MEMBERS.phone2, MEMBERS.lastvisit, MEMBERS.email, 
    MEMBERS.datejoin, MEMBERS.dateexpire, MEMBERS.daterenewal, 
    CHKINS.checkin, SITES.sitename, MEMBERS.relationship, MEMBERS.mtypeid
FROM            
    MEMBERS 
INNER JOIN
    CHKINS ON MEMBERS.memid = CHKINS.memid 
INNER JOIN
    EMPLOYEES ON MEMBERS.employeeid = EMPLOYEES.employeeid 
INNER JOIN
    SITES ON MEMBERS.siteid = SITES.siteid 
INNER JOIN
    MEMBERTYPES ON MEMBERS.mtypeid = MEMBERTYPES.mtypeid
WHERE
    (CHKINS.ischeckin = 'True') 
    AND (CHKINS.checkin BETWEEN @rvStartDate AND DATEADD(day, 45, @rvStartDate)) 
    AND (MEMBERS.relationship = 'M') 
    AND (MEMBERS.status = 'A') 
    AND (MEMBERS.mtypeid = '1' OR MEMBERS.mtypeid = '10' OR
         MEMBERS.mtypeid = '12' OR MEMBERS.mtypeid = '22' OR
         MEMBERS.mtypeid = '28' OR MEMBERS.mtypeid = '32' OR
         MEMBERS.mtypeid = '33' OR MEMBERS.mtypeid = '34' OR
         MEMBERS.mtypeid = '35' OR MEMBERS.mtypeid = '36' OR
         MEMBERS.mtypeid = '40' OR MEMBERS.mtypeid = '48') 
    AND (MEMBERS.datejoin BETWEEN @rvStartDate AND @rvEndDate)

Answer 1

I don't know all the ins and outs of your database schema but it appears to answer your question, unless you want additional columns in your report you don't need some of the joins. Basically you want to use a group by with a count, it should perform much faster than a sub-query for you. Seeing you said you are still learning/fairly new in one of your comments. I did change around a couple of things regarding your structure for my taste. One change is I used table abbreviations, eg. when you put the table in the from area simply add a space and then an abbreviation (e.g. m for members). The other thin is switching the list of OR for mtypid to a in statement which essentially does the same thing. Note that a comment is preceeded by -- so if you past the following code in your SSMS query window you should see a few more comments that I find useful tips for making code readable.

The trick when using the group by and joining multiple tables is to understand what is a one to many and a one to one relationship. If it is a one to many e.g. in the case of CHKINS table and that is what you want to count it is important to use only one to one for the members tables for other tables (e.g. membertypes) or when using count ensure you use a unique id for CHKINS in the count. Given your question didn't ask about using anything in the employees, membertypes, or sites tables you can simply remove them leaving a direct one to many relationship between members and chckins. After that for all the columns that are apart of the one side of the relationship that you also want to see include those in the group by.

SELECT        
    FirstName = m.fname  --m.fname AS Expr1 can chane to ,FirtsName = m.fname or ,m.fname AS FisrtName if you don't want Expr1
    ,LastName = m.lname  --m.lname AS Expr2 Again can replace Expr2 with something more meaninglfull if desired e.g. LastName (also is this exact same as e.lname ?  do you want both?)
    ,m.description
    ,m.phone1
    ,m.phone2
    ,m.lastvisit
    ,m.email
    ,m.datejoin
    ,m.dateexpire
    ,m.daterenewal
    ,m.relationship
    ,m.mtypeid
    ,m.status
    ,CountOfCheckins = COUNT(*)  --If you know the PrimaryKey for CHKINS you could aso do COUNT(DISNTINCT c.Id)
FROM            
    MEMBERS m
    INNER JOIN CHKINS c
    ON m.memid = c.memid 
    AND c.ischeckin = 'TRUE'
    --AND c.checkin BETWEEN @rvStartDate AND DATEADD(day, 45, @rvStartDate)  --only include if you want to also limit the checkins by the same date range
WHERE
    m.relationship = 'M'
    AND m.status = 'A' --if you want all statuses comment this line by adding -- in front
    AND m.mtypeid IN ('1','10','12','22','28','32','33','34','35','36','40','48')
    --AND m.mtypeid IN (1,10,12,22,28,32,33,34,35,36,40,48) --uncomment and use this line instead of above if m.mtypeid is a numeric data type (int, smallint, tinyint, etc.)
    AND m.datejoin BETWEEN @rvStartDate AND @rvEndDate
GROUP BY
    m.fname
    ,m.lname
    ,m.description
    ,m.phone1
    ,m.phone2
    ,m.lastvisit
    ,m.email
    ,m.datejoin
    ,m.dateexpire
    ,m.daterenewal
    ,m.relationship
    ,m.mtypeid
    ,m.status
ORDER BY
    m.fname
    ,m.lastname
    --this sorts list by First name then Last Name switch order if you want last first
    --or you can simply put CountOfCheckins to order by the least to greatest checkins or CountOfCheckins to get the greatest to the least

Answer 2

You could get the count of valid checkins from the CHKINS table by adding a sub-query in your select list that selects the count of records that match the MEMBERS.memid, where ischeckin is True

SELECT EMPLOYEES.lname
    ,EMPLOYEES.fname
    ,MEMBERS.fname AS Expr1
    ,MEMBERS.scancode
    ,MEMBERS.lname AS Expr2
    ,MEMBERTYPES.[description]
    ,MEMBERS.phone1
    ,MEMBERS.phone2
    ,MEMBERS.lastvisit
    ,MEMBERS.email
    ,MEMBERS.datejoin
    ,MEMBERS.dateexpire
    ,MEMBERS.daterenewal
    ,MEMBERS.relationship
    ,MEMBERS.mtypeid
    ,CHKINS.checkin
    ,SITES.sitename
    ,(SELECT COUNT(*) FROM CHKINS WHERE CHKINS.memid = MEMBERS.memid AND CHKINS.ischeckin = 'True') AS CheckinCount
FROM MEMBERS 
    INNER JOIN CHKINS ON MEMBERS.memid = CHKINS.memid
    INNER JOIN EMPLOYEES ON MEMBERS.employeeid = EMPLOYEES.employeeid
    INNER JOIN SITES ON MEMBERS.siteid = SITES.siteid 
    INNER JOIN MEMBERTYPES ON MEMBERS.mtypeid = MEMBERTYPES.mtypeid
WHERE (CHKINS.ischeckin = 'True')
    AND (CHKINS.checkin BETWEEN @rvStartDate AND DATEADD(day, 45, @rvStartDate)) 
    AND (MEMBERS.relationship = 'M') 
    AND (MEMBERS.[status] = 'A')
    AND (MEMBERS.mtypeid = '1' OR 
         MEMBERS.mtypeid = '10' OR
         MEMBERS.mtypeid = '12' OR
         MEMBERS.mtypeid = '22' OR
         MEMBERS.mtypeid = '28' OR
         MEMBERS.mtypeid = '32' OR
         MEMBERS.mtypeid = '33' OR
         MEMBERS.mtypeid = '34' OR
         MEMBERS.mtypeid = '35' OR
         MEMBERS.mtypeid = '36' OR
         MEMBERS.mtypeid = '40' OR
         MEMBERS.mtypeid = '48')
    AND (MEMBERS.datejoin BETWEEN @rvStartDate AND @rvEndDate)