using join with case statement-CodePudding

I am trying to do a join with a case statement.

My query below, what I am trying to do is join based on this condition: "if PROGRAM IS 28 THEN CHECK FOR 2 THINGS (and t1.DrOfrecord = 'T' or T1.PrimClin = T) IF NOT CHECK FOR ONE (PrimClin = T)"

Attempt #1:

SELECT t1.DrOfRecord,  t1.*
FROM FD__PROGRAM_CLIENT AS T0
LEFT OUTER JOIN FD__STAFF_ASSIGNED AS T1 ON T0.OP__DOCID = T1.programAdmitKey
                                         AND T1.DrOfRecord = 'T'
      (case when t0.PgmKey = 28 then OR  t1.PrimClin = 'T' end )

Attempt #2:

SELECT t1.DrOfRecord, t1.*
FROM FD__PROGRAM_CLIENT AS T0
LEFT OUTER JOIN FD__STAFF_ASSIGNED AS T1 ON T0.OP__DOCID = T1.programAdmitKey 
                                         AND CASE WHEN t0.PgmKey = 28 
                                                      THEN t1.DrOfRecord = 'T' OR t1.PrimClin = 'T' 
                                                      ELSE t1.PrimClin = 'T' 
                                             END

CodePudding user response：

You can do it without CASE WHEN like:

SELECT t1.DrOfRecord,  t1.*
FROM  FD__PROGRAM_CLIENT AS T0
LEFT OUTER JOIN FD__STAFF_ASSIGNED AS T1 
  ON (T0.PgmKey = 28 AND (t1.DrOfrecord = 'T' or T1.PrimClin = T) )
     OR (T0.PgmKey <> 28 AND (PrimClin = T) )

CodePudding user response：

The syntax of your query is incorrect. You cannot use a case statement inside the ON clause of a join like that.

Here is the correct syntax for the query:

SELECT t1.DrOfRecord, t1.*
FROM FD__PROGRAM_CLIENT AS T0
LEFT OUTER JOIN FD__STAFF_ASSIGNED AS T1
ON T0.OP__DOCID = T1.programAdmitKey
AND (T1.PrimClin = 'T' OR (T1.DrOfrecord = 'T' AND T0.PgmKey = 28))

If I understand your logic correctly you want all records that satisfy PrimClin = 'T' or the two other conditions.