I want to do a full join in order to include all the data from both tables.
First, I have to make a full join on Contract Change id for both tables. but as you can probably see, there are three rows in table 2 that won’t join. These three rows should be joined by contract id instead. What is the best way to do this? Would adding a second join clause over contract id on Quicksight solve this problem?
A better way to do this is to include a change table (where change_id is primary key) and start your query from there, so the query don’t miss any raws.
select * from change
left join table1 change.id = table1.change_id
left join table2 change.id = table2.change_id
Hi @mateoleon210 - The solution provided by @neelay is good one. As it is doing left join which ensure that you are not going to lose any record from the first table.
I did not understand this solution. You are suggesting to change the dataset structure and create a new table with another id?
for example if change.id= (row number of table 2)
then won’t we need to add the change_id column to table 1 also? How to do this?