Need help in SQL query

Question

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 14-Jul-20 1:37am

Pro86

Updated 14-Jul-20 2:22am

Add a Solution

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

2 solutions

Solution 1

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

Posted 14-Jul-20 1:49am

ZurdoDev

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

MadMyche · Accepted Answer · 2020-07-14T02:22:00

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)
                 )