SQL, Memory tables with JOIN

            
 
/*All sessions*/ 
DECLARE @Sessions TABLE ( 
  Id           int, 
  Name         varchar(150), 
  Attending    int, 
  NotAttending int, 
  NotResponded int); 

INSERT INTO @Sessions 
            (Id, 
             Name, 
             Attending, 
             NotAttending, 
             NotResponded) 
(SELECT CustomerFocusSessions.Id, 
        CustomerFocusSessions.Name, 
        ’0’, 
        ’0’, 
        ’0’ 
 FROM   CustomerFocusSessions) 





/*Attending*/ 
DECLARE @Attending TABLE ( 
  SessionId int, 
  Total     int); 

INSERT INTO @Attending 
            (SessionId, 
             Total) 
(SELECT CustomerFocusSessions.Id          AS SessionId, 
        COUNT(CustomerFocusRecipients.Id) AS Total 
 FROM   CustomerFocusRecipients 
        INNER JOIN CustomerFocusSessions 
          ON CustomerFocusRecipients.SessionId = CustomerFocusSessions.Id 
 WHERE  ( CustomerFocusRecipients.Attending = 1 ) 
 GROUP  BY CustomerFocusSessions.Id) 

/*Join Attending*/ 
UPDATE s 
SET    s.Attending = a.total 
FROM   @Sessions s 
       INNER JOIN @Attending a 
         ON s.Id = a.SessionId 





/*NOT Attending*/ 
DECLARE @NotAttending TABLE ( 
  SessionId int, 
  Total     int); 

INSERT INTO @NotAttending 
            (SessionId, 
             Total) 
(SELECT CustomerFocusSessions.Id          AS SessionId, 
        COUNT(CustomerFocusRecipients.Id) AS Total 
 FROM   CustomerFocusRecipients 
        INNER JOIN CustomerFocusSessions 
          ON CustomerFocusRecipients.SessionId = CustomerFocusSessions.Id 
 WHERE  ( CustomerFocusRecipients.Attending = 0 ) 
 GROUP  BY CustomerFocusSessions.Id) 

/*Join NOT Attending*/ 
UPDATE s 
SET    s.NotAttending = a.total 
FROM   @Sessions s 
       INNER JOIN @NotAttending a 
         ON s.Id = a.SessionId 




/*NOT NotResponded*/ 
DECLARE @NotResponded TABLE ( 
  SessionId int, 
  Total     int); 

INSERT INTO @NotResponded 
            (SessionId, 
             Total) 
(SELECT CustomerFocusSessions.Id          AS SessionId, 
        COUNT(CustomerFocusRecipients.Id) AS Total 
 FROM   CustomerFocusRecipients 
        INNER JOIN CustomerFocusSessions 
          ON CustomerFocusRecipients.SessionId = CustomerFocusSessions.Id 
 WHERE  ( CustomerFocusRecipients.Attending IS NULL ) 
 GROUP  BY CustomerFocusSessions.Id) 

/*Join NOT Attending*/ 
UPDATE s 
SET    s.NotResponded = a.total 
FROM   @Sessions s 
       INNER JOIN @NotResponded a 
         ON s.Id = a.SessionId 







/*All results*/

SELECT Id, 
       Name, 
       Attending, 
       NotAttending AS [Not Attending], 
       NotResponded AS [Not Responded] 
FROM   @Sessions