join and case

Vicki 46 Reputation points
2022-09-29T20:29:09.833+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Viorel 112.9K Reputation points
    2022-09-29T20:33:49.737+00:00

    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)  
    
    0 comments No comments

  2. Erland Sommarskog 102.3K Reputation points
    2022-09-29T21:33:08.157+00:00

    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.

    0 comments No comments

  3. NikoXu-msft 1,911 Reputation points
    2022-09-30T05:52:23.107+00:00

    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".

    0 comments No comments