5 Replies Latest reply on Jul 14, 2013 12:13 PM by Matt Lutton

    Left Join with Custom SQL

    Serge GABRIEL
    Serge GABRIEL Jun 14, 2012 6:06 AM

      Hello!

       

      I try to create a Custom SQL Data Source, joining two data sources.

       

      My code is the following:

       

      SELECT
        ['Pricing component-Tableau$'].[CatMan] AS [CatMan],
        ['Pricing component-Tableau$'].[Component] AS [Component],
        ['Pricing component-Tableau$'].[Cost] AS [Cost],
        ['Pricing component-Tableau$'].[Date] AS [Date],
        ['Pricing component-Tableau$'].[ID] AS [ID Action],
        ['Pricing component-Tableau$'].[ID Component] AS [ID Component],
        ['Pricing component-Tableau$'].[S/0] AS [S/0],
        ['Pricing component-Tableau$'].[Supplier] AS [Supplier Pricing],
        ['Details Tasks$'].[End] AS [End Gate],
        ['Details Tasks$'].[Plan/Real/Baseline] AS [Actual/Plan],
        ['Details Tasks$'].[Supplier] AS [Supplier Tasks]
      FROM ['Pricing component-Tableau$']
      LEFT OUTER JOIN ['Details Tasks$']
      ON  ['Details Tasks$'].[Supplier]=['Pricing component-Tableau$'].[Supplier]
      WHERE ['Details Tasks$'].[Type of task]="Gate"
      AND ['Details Tasks$'].[Description]="Secured Savings"
      AND ['Details Tasks$'].[Plan/Real/Baseline] IN("Real","Planed")
      AND   ['Pricing component-Tableau$'].[ID]=['Details Tasks$'].[ID Action]
      AND   ['Pricing component-Tableau$'].[ID Component]=['Details Tasks$'].[ID Comp]


      It almost gives me the right results.

      But I have a single problem: Not all of the supplier of the Data base "Pricing component-Tableau" are listed in the data base "Details Tasks".

      For these suppliers, I would like to visualize also the Cost of the component over time (information to be found in the "Pricing component-Tableau"data base)... That's why I try to do a "Left join".

      How can I do this?

      What is wrong with my code? (Until now, it gives me all the right informations that I need to use in Tableau but just for the suppliers which are listed in both data bases).

       

      Thanks a lot for your help!

      Best regards,

        • 1. Re: Left Join with Custom SQL
          Serge GABRIEL
          Serge GABRIEL Jun 14, 2012 7:50 AM (in response to Serge GABRIEL)

          Here is an example of what I want to do...

          • 2. Re: Left Join with Custom SQL
            Andrew Watson
            Andrew Watson Jun 28, 2012 7:32 AM (in response to Serge GABRIEL)

            Hi Serge

             

            I think the way you have written the SQL is causing your problem. Try the following instead, it should work.

             

            Andrew

             

            SELECT
              ['Pricing component-Tableau$'].[CatMan] AS [CatMan],
              ['Pricing component-Tableau$'].[Component] AS [Component],
              ['Pricing component-Tableau$'].[Cost] AS [Cost],
              ['Pricing component-Tableau$'].[Date] AS [Date],
              ['Pricing component-Tableau$'].[ID] AS [ID Action],
              ['Pricing component-Tableau$'].[ID Component] AS [ID Component],
              ['Pricing component-Tableau$'].[S/0] AS [S/0],
              ['Pricing component-Tableau$'].[Supplier] AS [Supplier Pricing],
              ['Details Tasks$'].[End] AS [End Gate],
              ['Details Tasks$'].[Plan/Real/Baseline] AS [Actual/Plan],
              ['Details Tasks$'].[Supplier] AS [Supplier Tasks]
            FROM ['Pricing component-Tableau$']
            LEFT OUTER JOIN ['Details Tasks$']
            ON  ['Details Tasks$'].[Supplier]=['Pricing component-Tableau$'].[Supplier]

            AND  ['Details Tasks$'].[Type of task]="Gate"
            AND ['Details Tasks$'].[Description]="Secured Savings"
            AND ['Details Tasks$'].[Plan/Real/Baseline] IN("Real","Planed")

            AND  ['Pricing component-Tableau$'].[ID]=['Details Tasks$'].[ID Action]
            AND   ['Pricing component-Tableau$'].[ID Component]=['Details Tasks$'].[ID Comp]

            • 3. Re: Left Join with Custom SQL
              Matt Lutton
              Matt Lutton Employee Jun 2, 2013 3:18 PM (in response to Serge GABRIEL)

              I wish a reply would've been given in this scenario, so new folks could learn from the thread...

              • 4. Re: Left Join with Custom SQL
                Toby Erkson
                Toby Erkson Ambassador Jun 3, 2013 7:43 AM (in response to Matt Lutton)

                Preachin' to the choir!  I agree, very irritating.

                • 5. Re: Left Join with Custom SQL
                  Matt Lutton
                  Matt Lutton Employee Jul 14, 2013 12:13 PM (in response to Toby Erkson)

                  My understanding is that LEFT and LEFT OUTER are the same, at least in pure SQL terms. How Tableau interprets the SQL, I am not certain. But I'm also a newbie and could be wrong about everything!