How to use a case statement to determine which field to left join on

Question

I have a query that needs to be able to left join a field at the right place given the condition that the field is equal to a variable.

Something like:

CASE WHEN challenges.userID = $var
LEFT JOIN challengesRead ON challenges.userID = challengesRead.userID
CASE WHEN challenges.opponentID = $var
LEFT JOIN challengesRead ON challenges.opponentID = challengesRead.userID

I think I am on the right track but I'm not sure how to put the query together. Thanks

score 10 · Accepted Answer · answered Sep 29 '11 at 15:49

10

Something like this may work

LEFT JOIN challengesRead 
ON challenges.userID = CASE 
WHEN challenges.userID = $var THEN challengesRead.userID 
WHEN challenges.opponentID = $var THEN challenges.opponen END

answered Sep 29 '11 at 15:49

Raj More

47,048
33
131
198

score 1 · Answer 2 · answered Sep 29 '11 at 16:01

It seems you want this:

LEFT JOIN challengesRead 
  ON CASE WHEN challenges.userID = $var
            THEN challenges.userID 
          WHEN challenges.opponentID = $var
            THEN challenges.opponentID
     END = challengesRead.userID

which can be simplified to:

LEFT JOIN challengesRead 
  ON $var = challengesRead.userID
  AND ( challenges.userID = $var
     OR challenges.opponentID = $var
      )

How to use a case statement to determine which field to left join on

2 Answers2

Linked