10 Replies Latest reply on May 25, 2016 8:14 AM by Jane Mamas

    Joining databases - one-to-many connection

    Jane Mamas

      Hi, all! I was looking for the solution and read lots of articles about data blending, joining and unions - still couldn't find the answer

      So I was hoping to get some help here

       

      I have a set of relational databases (tables) on SQL server and I try to join them. The idea is to filter the data from Table A based on values in table F and G. The tables don't have a shared primary key, but they have some common fields that allow me to connect them (see the green arrows on the picture).

      Tableau_example.JPG

       

      I used left join to connect the tables and it works. The only problem is that I need to create three copies of each table I connect further down. For example, table F. Any value from this table is now shown in three different columns and I have to create a calculated field to put them all together (to use as a filter or in calculations). Not a big deal, but I have lots of those small tables with extra bits of information and every time I have to connect three copies.

       

      I was wondering if there any better way to design these connections?

       

      Tables B, C and D have different number of fields and only some of them are in common, so I guess sql 'UNION ALL' or similar is not an option here.

       

      Thank you in advance and any ideas would be much appreciated

       

      Tableau Desktop 9.3

       

      UPD: The best solution I found was to use a custom SQL to union 3 databases (B, C, D) using shared fields.

      But I still preferred to stick to the standard Left Join option with the three copies of each table (exactly what I've done from the very beginning, see the picture above).

       

      SELECT bin_tran_id, site_sid, dist_cr_id as Id

      FROM [dbo].[dist_credit] [[dist_credit]

      Union

      SELECT bin_tran_id, site_sid, dist_id as Id

      FROM [dbo].[distribution] [distribution]

      Union

      SELECT bin_tran_id, site_sid, kit_dist_id as Id

      FROM [dbo].[kit_distribution] [kit_distribution]