3 Replies Latest reply on Mar 28, 2016 2:47 PM by kettan

    Regarding Joins

    Tharashasank Davuluru

      Does a left join will create cartesian or cross product when we join with multiple tables? if so how to avoid this?

        • 1. Re: Regarding Joins
          Derrick Austin

          Hey Tharashasank,

           

          This can definitely happen do to bad joins.

           

          Basically, this occurs when one (or more) of the joined in tables have more than one record per join condition.

          When this happens, the data is multiplied, as you are seeing.

           

          You will need to either create tables with a single, unique value on the join condition, or use Custom SQL (or a view in the database)

           

          In the SQL side, you can fix this by joining subqueries with DISTINCT on the values you need (or MAX(), if there are multiple values and you only need one)

           

          Derrick Austin

          1 of 1 people found this helpful
          • 2. Re: Regarding Joins
            Bora Beran

            If relationship is one to many e.g. employee has multiple kids then it will replicate the rows. But this is not really a cross join. For a real cross joins happen one needs to use CROSS JOIN keyword in SQL or there needs to be no join key e.g. a join condition that always returns true such as 1=1. Tableau won't do this through the join diagram since you are required to provide join keys.

             

            If you don't want this to happen, you can aggregate tables before joining e.g. in the case of employee and kids, you can write kids as custom SQL  and pick one kid of your choice using a function like MIN or MAX and join that custom SQL pill with employee table.  Or you can blend tables instead of join, which will aggregate the secondary automatically.

            1 of 1 people found this helpful
            • 3. Re: Regarding Joins
              kettan

              For a real cross joins happen one needs to use CROSS JOIN keyword in SQL or there needs to be no join key e.g. a join condition that always returns true such as 1=1. Tableau won't do this through the join diagram since you are required to provide join keys.

              CROSS JOIN with Tableau's join dialog shows how well Tableau performs with 1=1 cross joins via the built-in join dialog. Dashboard author must of course ensure that 1=1 dummy keys are added & populated in both tables first.