I have tables three tables: #T, #T1, #Maintable, for which I'll attach the corresponding DDL.

Create table  #T ( id int , reason varchar (50));    

insert into #T Values (1,'Texas for live music');
insert into #T Values (1,'Texas for BBQ');
insert into #T Values (2,'Wisconsin for dairy products');
insert into #T Values (2,'Wisconsin for Rock');
insert into #T Values (2,'Wisconsin for Bird');
insert into #T Values (3,'North Carolina for Pepsi');
insert into #T Values (4,'Missouri for Forest');
insert into #T Values (5,'Oklohoma for cowboy');
insert into #T Values (5,'Oklohoma for Native Americans');
insert into #T Values (5,'Oklohoma for oil and gas');
   
Create table  #T1 ( id int , reason varchar (50));   

insert into #T1 Values (1,'Texas for live music,BBQ');
insert into #T1 Values (2,'Wisconsin for dairy products, rock,bird');
insert into #T1 Values (3,'North Carolina for Pepsi');
insert into #T1 Values (4,'Missouri for Forest');
insert into #T1 Values (5,'Oklohoma for Native Americans,oil and gas');

Create table #MainTable (id int, State varchar(20),Capital varchar(30),Governer varchar(30)); 

Insert into #Maintable values (1,'Texas','Austin','A');
Insert into #Maintable values (2,'Wisconsin','Madison','B');
Insert into #Maintable values (3,'North Carolina','Releigh','C');
Insert into #Maintable values (4,'Missouri','Jefferson City','D');
Insert into #Maintable values (5,'Oklohoma','Oklohoma city','E');

Expected Output

I have a couple of tables, based on the criteria I will be filtering records from table #T and joining with other tables to get more columns but with the help of cte, I am not able to filter. if first table #T has more than one Id then we will be using reason from another table #T1. If it has only one Id then we will be using Reason from Table #T and finally, we will join with #main table to get other records. I have added an image describing more. Help is much appreciated. All those temp tables we can test

And the scenario is:

• If reason appears more than once in #T table use #T1 table
• If reason appears only once in the #T table use #T only, this is the first table

Here's my coding attempt:

with cte as (
    select *, ROW_NUMBER() over (partition by id order by id) rn 
    from #T 
)
select mt.id, state, capital, Governer,
       case when c.rn > 1 
            then #t1.reason 
            else c.reason 
       end as reason
from cte c 
join #t1           on c.id = #t1.id 
join #maintable mt on c.id = mt.id

I am getting more results, I was expecting only 5 records. I guess there is some issue in my row_number. Help is appreciated.

CodePudding user response：

You should first extract the ranking value from the table "#T" for each ID. Then you can use the COALESCE function in combination with LEFT JOIN operations to solve your problem:

WITH #T_ranked AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY id ORDER BY reason) AS rn 
    FROM #T
)
SELECT m.id, 
       COALESCE(#T1.reason, #T_ranked.reason) AS Reason,
       m.State, 
       m.Capital, 
       m.Governer
FROM      #Maintable m
LEFT JOIN #T1 ON m.id = #T1.id
LEFT JOIN #T_ranked ON m.id = #T_ranked.id AND #T_ranked.rn = 1

The LEFT JOIN operations will make your ids in the "MainTable" be kept, while the COALESCE function will make the first non-null argument to be the value for the "Reason" new field, in the specific case it will check whether "#T1.Reason" is null, if it is then it will assign "#T2.Reason". Values from "#T.Reason" which have an existing correspondent in "#T1.Reason" will never be selected in this way.

Check the demo here.