Click here to Skip to main content
15,070,139 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Need help in SQL query - I am trying to add case statement in SQL Inner join after On keyword. My query is as below . But it gives me syntax error

SELECT A.*
FROM 
ABC A
INNER JOIN
XYZ B ON
CASE A.ID WHEN 7 THEN A.name = B.name
ELSE 
	A.surname = B.surname
	END
	AND A.Id = B.Id



What I have tried:

SELECT A.*
FROM 
ABC A
INNER JOIN
XYZ B ON
CASE A.ID WHEN 7 THEN A.name = B.name
ELSE 
	A.surname = B.surname
	END
	AND A.Id = B.Id
Posted
Updated 14-Jul-20 2:22am
Comments
CHill60 14-Jul-20 8:19am
   
You will get more help if you include sample data with your question and what you expect the result to be for that sample data. A better description of what you are trying to do always helps too

As previously stated, the CASE can only be on one side of the equation. What you could try to do is to try to break out those conditions into AND and OR clauses like this
SQL
SELECT A.*
FROM       ABC A
INNER JOIN XYZ B  ON (A.Id = B.Id)
                 AND (  (A.ID = 7 AND A.name = B.name)
                     OR (A.surname = B.surname)
                 )
If you only want to JOIN on the surname when A.ID does not equal 7 then you would use this query
SQL
SELECT A.*
FROM       ABC A
INNER JOIN XYZ B  ON (A.Id = B.Id)
                 AND (  (A.ID = 7 AND A.name = B.name)
                     OR (A.ID <>7 AND A.surname = B.surname)
                 )
   
v2
Comments
Richard Deeming 15-Jul-20 9:04am
   
The question suggests that records shouldn't match on surname if ID is 7, so you'd probably need an A.ID != 7 inside the surname branch:
AND 
(
    (A.ID = 7 AND A.name = B.name)
OR
    (A.ID != 7 AND A.surname = B.surname)
)
MadMyche 15-Jul-20 10:01am
   
Have added onto the original answer to reflect this
You can't do it that way. You can use case but only on one side of the equals. For example:

SQL
CASE A.ID WHEN 7 THEN A.name ELSE A.surname END = B.name


You might be able to put the case on both sides of the = sign (2 cases)

Not sure if this works, if it doesn't you'll probably need two joins

SQL
CASE A.ID WHEN 7 THEN A.name ELSE A.Surname END = CASE A.ID WHEN 7 THEN B.name ELSE B.surname END
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900