By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tableA (
id_A varchar(20)
);
insert into tableA values
( 'insight1'),
( 'insight2'),
( 'insight3'),
( 'insight4'),
( 'insight5');
create table mappingAB (
id_A varchar(20),
id_B varchar(20)
);
insert into mappingAB values
( 'insight1' , 'obs1'),
( 'insight1' , 'obs2'),
( 'insight2' , 'obs1'),
( 'insight2' ,'obs2'),
( 'insight2' , 'obs3'),
( 'insight3' ,'obs1');
create table tableB (
id_B varchar(20),
source varchar(20)
);
insert into tableB values
( 'obs1' , 'AWS'),
( 'obs2' , 'GCP'),
( 'obs3' , 'GCP');
Records: 5 Duplicates: 0 Warnings: 0
Records: 6 Duplicates: 0 Warnings: 0
Records: 3 Duplicates: 0 Warnings: 0
select A.id_A, source
from tableA A
inner join mappingAB AB on AB.id_A = A.id_A
inner join tableB B on AB.id_B = B.id_B
| id_A | source |
|---|---|
| insight1 | GCP |
| insight1 | AWS |
| insight2 | GCP |
| insight2 | GCP |
| insight2 | AWS |
| insight3 | AWS |
with cte as (
select A.id_A, source
from tableA A
inner join mappingAB AB on AB.id_A = A.id_A
inner join tableB B on AB.id_B = B.id_B
)
select id_A,
MAX(case when source = 'AWS' then 1 else 0 end) as AWS,
MAX(case when source = 'GCP' then 1 else 0 end) as GCP
from cte
group by id_A
| id_A | AWS | GCP |
|---|---|---|
| insight1 | 1 | 1 |
| insight2 | 1 | 1 |
| insight3 | 1 | 0 |