Joining tables

Drew,  Thank You for the reply. It's not homework, I am new to SQL and started to learn from w3schools.
           This is something I got from one of the online tutorial and struck building it.

Count 2 and 3 are inactive(active = n) for a given column 1 and column 2.
As we are changing c2=c1 in column 2, we are making sure active status of column 1& column 2 is consistent.

If you got it from a tutorial, then it's homework. Go back to the lesson where you got this, and it should give you an idea about how to approach this, assuming that the tutorial is well written.

If you are still having problems, show us what you've tried and we can help you.

Drew

Hi,
Welcome to SQLcentral. Drew is absolutely right and try to solve on your own . Also post create and insert statement so will help create test data and consume time.

create table table_a
(
Column1 varchar(200),
Column2 varchar(200),
active varchar(200)
  );

insert into table_a values('a','c1','y');
insert into table_a values('a','c2','n');
insert into table_a values('b','c1','n');
insert into table_a values('b','c2','y');
insert into table_a values('c','c1','y');
insert into table_a values('c','c2','y');
insert into table_a values('d','c1','n');
insert into table_a values('d','c2','n');

create table table_b
(
Column1 varchar(200),
Column2 varchar(200),
count int
  );

insert into table_b values('a','c1',1);
insert into table_b values('a','c2',2);
insert into table_b values('b','c1',3);
insert into table_b values('b','c2',4);
insert into table_b values('c','c1',5);
insert into table_b values('c','c2',6);
insert into table_b values('d','c1',7);
insert into table_b values('d','c2',8);

select Column1,Column2,active,count
from
(
select distinct Column1,Column2,active,count,
rank()over(partition by Column1,Column2 order by active desc) as rnk
from
(
select act.Column1,
case when act.Column2='c2' then 'c1' else act.column2 end as Column2,
act.active,
cnt.count
from table_a act
inner join table_b cnt
on act.Column1=cnt.Column1
and act.Column2=cnt.Column2
)ranking
)eli
where rnk=1


Output:

6 rows