Latest Enterprise DNA Initiatives

Merge queries join

I have four tables and I would like to merge queries between tables, I want to know which join of kind (Left outer, Right outer, Full outer, Inner ) I do ? the tables and relation ship are given below

Table 1 (Base Table) one
Table 2 many , one
Table 3 many , many
Table 4 one

Hello @khurram,

Thank You for posting your query into the Forum.

Firstly, I would like to say it doesn’t matter which type/kind of Join to follow if your data is completely designed in proper manner keeping in mind the “Star Schema”. All your records will by default will get matched and you will get the results that you want.

Now, there’s a lot to ponder around because as you said some of your tables contains “Many-to-Many” or “Many-to-One” relationships and also we’ve no idea how does your relationship flow from one table to another.

The fundamental or the best practice way is to have “One-to-Many” relationship between your Dimension Table and Fact Table (Waterfall Effect as said by Sam). There are lots and lots of videos of available on our education portal created by Sam where he guides us how to create, design and organize the data model. I sincerely recommend you to go through the course “Advanced Data Transformations & Modelling” which is exclusively created keeping in mind the “Best Data Modelling Practices”. In this course, he has also showed us whichever type/kind you of Join you select you’ll achieve the same results provided your data is designed correctly.

I’m also providing a few links from that section of the course regarding your query.

Hoping you find this useful.

Thanks & Warm Regards,

Hi @khurram, did the response provided by @Harsh help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

Hi @khurram, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!