Full outer join between two tables

Dear @rvgfox ,

In my opinion this is the only time when you really need a custom view.
If you need to ‘Blow up’  / create a carthesian product of the table by (outer) joining, because your goals is to increase the amount of records then there is no other way that I know of to do this.

Lookups never create more records in the destination table.

Hope this helps

= Daniel

Thanks @daniel 

My approach it’s to use a “simple custom view” like this:

SELECT Id FROM A

UNION

SELECT Id FROM B

And create a table based on this view and make the lookups and transformations needed.

What do you think?

Hi @rvgfox You could use a junk dimension table. take the id's from the both tables. and then use lookups to join the fields to the table if you don't want to keep it in TimeXtender native

Hi @rvgfox 

Any type of outer join are only possible in custom views currently.

When you map two tables together with the same structure they get a union behind the scenes.

It will not give the look you have in the first post, it will be like this.

So using an custom view is preferable.

Personally, if I can avoid custom view I will. 

For a union I would not use a custom view as this can be done in TX native. 

By creating a table and mapping the fields from the table in destination columns you want them to be:

 

Then you can still do the lookups if you want and TX keeps tracking the data lineage and such as it normally would. Mistakes in the parameters can be made very quicky and for some views I might take a while.

In the case of a full outer join I'm not use it will work the same as unioning the table and then doing lookups though. If you need to create a carthesian product because that is what you need, then use a full outer join or a cross apply. If not, then my go to is to fix it ‘TimeXtender​​​​​​ Natively’ 

= Daniel