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