CODEWITHSUNDEEP
sqlamazon-redshift
user2330382
user2330382

Reputation: 19

Redshift Fill In Missing Rows in Table A from Table B

Please help! I am not sure if Redshift has an issue or if it is just me.

Table A has the below data:

Company_Name Report_Type Report_Number
Apple Sales 123
Apple Employees 804

Table B has the below data:

Report_Number Report_Type
123 Sales
804 Employees
654 Inventory
Revenue

I need the below output, i.e. to add missing report_types to Table A:

Company_Name Report_Type Report_Number
Apple Sales 123
Apple Employees 804
Apple Inventory 654
Apple Revenue

Upvotes: 0

Views: 149

Answers (1)

eshirvana
eshirvana

Reputation: 24568

select t2.Company_Name , t1.Report_Type 
from (select distinct Report_Type from TableB) t1
cross join (select distinct Company_Name from tableA) t2
where not exists (select 1 from tableA t3 
                  where t3.Company_Name = t2.Company_Name 
                    and t3.Report_Type = t1.Report_Type
                  );

Upvotes: 1

Related Questions