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.