blacksaibot blacksaibot - 1 month ago 5
SQL Question

Case within join's "ON" caluse

I'm trying to do something like this...

SELECT lc.Location, count(h.empID) as 'Count', lu.LBL
FROM swam.lookup lu
LEFT JOIN swam.empTable h
ON CASE
WHEN lu.sac is null
THEN lu.POS = h.POS
ELSE (
lu.POS = h.POS
and
lu.sac = h.sac)
INNER JOIN swam.Locations lc
ON h.LocationID = lc.LocationID
GROUP BY lc.Location, lu.LBL
ORDER BY lc.Location


So, if lu.sac is null, then only join the tables on one common column.
If it's not null, then it has to use both POS and SAC to join.

Is this possible?

Answer

Don't use case. Just use direct boolean logic:

FROM swam.lookup lu LEFT JOIN
     swam.empTable h
     ON lu.POS = h.POS AND
        (lu.sac is null OR lu.sac = h.sac) INNER JOIN
     swam.Locations fs
     ON h.LocationID = lc.LocationID

As a bonus, the database engine can also table advantage of an index on pos in either (or both) tables.