please help my query join two table

Question

HI T-sql Team,

I have created #A table inserted values and another table I have Tenant table

CREATE TABLE #A (NAME NVARCHAR(10),CODE BIGINT)
INSERT INTO #T(TENANTISO,TENENT_CODE) VALUES ('INDIA',1000000000001);
INSERT INTO #T(TENANTISO,TENENT_CODE) VALUES ('RSA',1000000000011);
INSERT INTO #T(TENANTISO,TENENT_CODE) VALUES ('AMERICA',1000000000015);

A table data

Tenant table data

My out below condition like below :
I need display all values Name and Code and
IF have name code in Tenant table then same color otherwise type SS value display in the not match
below is my outpu table

'RSA',1000000000011 and 'AMERICA',1000000000015 didnt availble in Tenant table data should those TYPE=SS value display in the out table

please help how to create query

Answer 1

Hi @Shanvitha ,

Please refer to:

    CREATE TABLE A (NAME NVARCHAR(10), code BIGINT)     
    INSERT INTO A (NAME, CODE)  
    VALUES ('INDIA', 1000000000001);    
    INSERT INTO A (NAME, CODE)  
    VALUES ('RSA', 1000000000011);    
    INSERT INTO A (NAME, CODE)  
    VALUES ('AMERICA', 1000000000015);    
    CREATE TABLE Tenant ([CODE] VARCHAR(13), [COLOR] VARCHAR(5), [TYPE] VARCHAR(2));    
    INSERT INTO Tenant ([CODE], [COLOR], [TYPE])  
    VALUES ('1000000000001', 'GREEN', 'TN'), ('1000000000018', 'RED', 'TN'), ('1000000000001', 'PIN', 'TN'), (NULL, 'BLACK', 'SS');  
      
    select a.[Name],a.Code,IIF(a.Code in (select Code from Tenant),t.[COLOR],(select [COLOR] from Tenant where [TYPE]='SS'))COLOR  
    from A a  
    left join Tenant t  
    on a.Code=t.Code  
      
    drop table A  
    drop table Tenant  

You can also use case when:

select a.[Name],a.Code,case when a.Code in (select Code from Tenant) then t.[COLOR]   
                      else (select [COLOR] from Tenant where [TYPE]='SS') end COLOR  
from A a  
left join Tenant t  
on a.Code=t.Code  

The code 1000000000001 has two colors. If you randomly choose one to return, please refer to：

with cte  
as(select a.[Name],a.Code,case when a.Code in (select Code from Tenant) then t.[COLOR]   
                      else (select [COLOR] from Tenant where [TYPE]='SS') end COLOR  
from A a  
left join Tenant t  
on a.Code=t.Code)  
,cte2 as(select*,row_number() over(partition by [Name],Code order by Code) rn from cte)  
  
select [Name],Code,COLOR from cte2 where rn<2  
order by Code  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

See below query for reference. If you don't get the correct result, please explain the correct and clear logic.

CREATE TABLE #A (NAME NVARCHAR(10), CODE BIGINT)

INSERT INTO #A (NAME, CODE)
VALUES ('INDIA', 1000000000001);

INSERT INTO #A (NAME, CODE)
VALUES ('RSA', 1000000000011);

INSERT INTO #A (NAME, CODE)
VALUES ('AMERICA', 1000000000015);

CREATE TABLE #T ([CODE] VARCHAR(13), [COLOR] VARCHAR(5), [TYPE] VARCHAR(2));

INSERT INTO #T ([CODE], [COLOR], [TYPE])
VALUES ('1000000000001', 'GREEN', 'TN'), ('1000000000018', 'RED', 'TN'), ('1000000000001', 'PIN', 'TN'), (NULL, 'BLACK', 'SS');

SELECT a.name, a.CODE, isnull(t.COLOR, 'BLACK')
FROM #A AS a
LEFT JOIN #T AS t ON a.CODE = t.CODE

---

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav