join and case

Question

Hi,

What I have missed from the syntax below ? Thanks

select a.company,
b.cust
from getmain a
left join getsub b
on case
when b.cust is not null then
a.company =b.company and a.cust =b.cust
else then
a.company =b.company
end

Answer 1

Probably this:

select a.company, b.cust  
from getmain a  
left join getsub b on a.company = b.company and (b.cust is null or a.cust = b.cust)  

Answer 2

What you have missed is that CASE is an expression that returns a single scalar value in any of the SQL Server data types.

Therefore you cannot have:

 then a.company =b.company   

While you can have boolean expressions in SQL Server, there is no boolean data type, so you cannot do this.

What you could do in theory:

   left join getsub b  
   case  
   when b.cust is not null then  
   iif(a.company =b.company and a.cust =b.cust, 1, 0)  
   else then iif(a.company =b.company, 1, 0)  
   end = 1  

But Viorel's rewrite looks better to me.

Answer 3

Hi @Vicki ,

CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.
For more details, please see:
https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-ver16
Also, I recommend you to try the following code：

select a.company,  
b.cust  
from getmain a  
left join getsub b on a.company = b.company and  a.cust =b.cust and b.cust is not null  
union all  
select a.company,  
b.cust  
from getmain a  
left join getsub b on a.company = b.company and b.cust is null  

Best regards
Niko

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".