SQL Server Join

Question

The query on which I am currently working returns the desired results but the problem is that I have to create an additional join with different TaskCounters for each extra column I want to add (which is of course not a rational decision).

The query is supposed to run on SQLServer as well as on an Access DB, so I do not need any special functions (like Pivot, CTE etc.) which are not supported in access. I need to revise that query so that the number of joins should be reduced as much as they can.

tblConsultations is the main table in which the entry for a particular child is recorded based upon the visit type (i.e. if we have 4 visits, we have 4 entries for the Consultations). The result column should display the values for those 4 visits and display NULL if there is no value against a visit.

I want to eliminate the need for extra join from the same table which I have to add for every additional column

The query is as follows:

SELECT Cast(SUBSTRING(tc2.ChildCounter, 7, LEN(tc2.ChildCounter)) AS NUMERIC) AS pkChildID
,tc2.VisitType
,tblQuitOffered.Result AS KWA_QuitOffered
,tblQuitReferral.Result As KWA_QuitReferral
FROM tblConsultations tc2
INNER JOIN tblChild tc ON tc2.ChildCounter = tc.ChildCounter
LEFT  JOIN tblDelivery td ON td.ChildCounter = tc.ChildCounter
LEFT JOIN (
SELECT ttr.ResultCounter
    ,ttr.ChildCounter
    ,tkt.VisitType
    ,ttr.Result
    ,ttr.TaskCounter
FROM tblTaskResults ttr
INNER JOIN tlkpKeyTasks tkt ON tkt.TaskCounter = ttr.TaskCounter
    AND tkt.TaskCounter IN (
        '001410'
        ,'001463'
        ,'001431'
        )

) AS tblQuitOffered ON tc2.VisitType = tblQuitOffered.VisitType
AND tblQuitOffered.ChildCounter = tc2.ChildCounter
LEFT JOIN (
SELECT  ttr.ChildCounter
    ,tkt.VisitType
    ,ttr.Result
FROM tblTaskResults ttr
INNER JOIN tlkpKeyTasks tkt ON tkt.TaskCounter = ttr.TaskCounter
    AND tkt.TaskCounter IN (
        '001411'
        ,'001464'
        ,'001432'
        )
) AS tblQuitReferral ON tc2.VisitType = tblQuitReferral.VisitType
AND tblQuitReferral.ChildCounter = tc2.ChildCounter
WHERE tc2.VisitType in (1, 2, 3, 4)
AND tc2.ConsDate BETWEEN '20130127' and '20130228'
ORDER BY tc2.ChildCounter,tc2.VisitType

The result is as follows:

pkChildID   VisitType     KWA_QuitOffered   KWA_QuitReferral
2224        1             No                No
2224        3             NULL              NULL
2224        4             NULL              NULL
2225        1             No                Yes
2225        2             Yes               Yes
2225        3             Yes               Yes
2225        4             NULL              NULL

Answer 1

If I understand correctly, you can include all the TaskCounter values you want in a single derived table, then use a CASE statement to assign your column values:

SELECT Cast(SUBSTRING(tc2.ChildCounter, 7, LEN(tc2.ChildCounter)) 
            AS NUMERIC) AS pkChildID
      ,tc2.VisitType

      ,MAX(CASE WHEN tktResults.TaskCounter IN (
                '001410'
               ,'001463'
               ,'001431'
               ) THEN tktResults.Result END
          ) AS KWA_QuitOffered

      ,MAX(CASE WHEN tktResults.TaskCounter IN (
                '001411'
               ,'001464'
               ,'001432'
               ) THEN tktResults.Result END
          ) AS KWA_QuitReferral

FROM  tblConsultations tc2
INNER JOIN tblChild tc 
  ON  tc2.ChildCounter = tc.ChildCounter
LEFT  JOIN tblDelivery td
  ON  td.ChildCounter = tc.ChildCounter

LEFT JOIN (
   SELECT ttr.ResultCounter
         ,ttr.ChildCounter
         ,tkt.VisitType
         ,ttr.Result
         ,ttr.TaskCounter
   FROM tblTaskResults ttr
   INNER JOIN tlkpKeyTasks tkt 
   ON tkt.TaskCounter = ttr.TaskCounter
       AND tkt.TaskCounter IN (
            '001410'
           ,'001463'
           ,'001431'
           ,'001411'
           ,'001464'
           ,'001432'
           )
   ) AS tktResults 
ON     tktResults.VisitType    = tc2.VisitType
   AND tktResults.ChildCounter = tc2.ChildCounter

WHERE tc2.VisitType in (1, 2, 3, 4)
  AND tc2.ConsDate BETWEEN '20130127' and '20130228'
GROUP BY 1, 2
ORDER BY tc2.ChildCounter,tc2.VisitType

If fact, you don't really need to make that a derived table (the LEFT JOIN (...) part), but I'm assuming you are doing that for performance reasons. As you add columns based on different values of TaskCounter, just add them to the join condition.

EDIT: Revised to use GROUP BY clause with MAX function to return unique rows by pkChildID and VisitType.

I'm using the "ordered list" style in the GROUP BY clause; if that isn't supported, you may need to specify it like this:

GROUP BY Cast(SUBSTRING(tc2.ChildCounter, 7, LEN(tc2.ChildCounter)) AS NUMERIC)
        ,tc2.VisitType

I also noticed you are sorting by a column that is not part of the SELECT clause. I'm not sure why but left it intact.

Answer 2

I was posting an answer some think like what BellevueBob posted.

I think you can use Nested iif()'s instead of 'case when'