Combining Sql Join With Count

I have two tables registered and attended, each with two columns: AttendentId and SessionId. I would like to query the count of AttendantId from these two tables individually for a

Solution 1:

You could use a FULL OUTER JOIN:

selectcoalesce(a.sessionid, r.sessionid) sessionid,
  count(r.AttendantId) countRegistered,
  count(a.AttendantId) countAttended
from registered r
fullouterjoin attended a
  on r.sessionid = a.sessionid
  and r.AttendantId = a.AttendantId
groupbycoalesce(a.sessionid, r.sessionid);

See SQL Fiddle with Demo

Solution 2:

SELECT
    ISNULL(ACount, 0),
    ISNULL(RCount, 0),
    X.SessionId
FROM
    (
    SELECT SessionId FROM Registered
    UNION -- implies DISTINCTSELECT SessionId FROM Attended
    ) X
    LEFT JOIN
    (SELECT COUNT(*) AS RCount, SessionId
     FROM Registered
     GROUPBY SessionId) R ON X.SessionId = R.SessionId
    LEFT JOIN
    (SELECT COUNT(*) AS ACount, SessionId
     FROM Attended 
     GROUPBY SessionId) A ON X.SessionId = A.SessionId

SQLFiddle

Solution 3:

Try this query

select 
   a.sessionId, 
   casewhen aCnt isnullthen0else acnt end, 
   casewhen bCnt isnullthen0else bcnt endfrom
   (select 
       sessionId, 
       count(*) aCNt 
   from 
       tbl1 
   groupby 
       sessionid) a
fulljoin
   (select 
       sessionId, 
       count(*) bCnt 
   from 
       tbl2 
   groupby 
       sessionid) b
on 
   a.sessionId = b.sessionid

SQL FIDDLE:

| SESSIONID | COLUMN_1 | COLUMN_2 |
-----------------------------------
|       SN1 |        2 |        1 |
|       SN2 |        1 |        0 |
|       SN3 |        1 |        1 |

Hope this helps....

Solution 4:

Assuming all session ID's exist in registered table

SELECTSum(CASEWHEN a.attendentid ISNOTNULLTHEN1ELSE0end) ASCount(registered), 
       Sum(CASEWHEN b.attendentid ISNOTNULLTHEN1ELSE0end) ASCount(attended), 
       a.sessionid 
FROM   registered a 
       INNERJOIN attended b 
               ON a.sessionid = b.sessionid 
GROUPBY a.sessionid