2 Replies Latest reply on May 5, 2013 12:23 PM by Russell Christopher

    COMPLEX SQL JOIN

    Stuart Wilson

      Hi everyone

       

      I hope someone could help me with this custom SQL query:

       

      SELECT *, 'ASL' AS [COMPANY], 'ACTUAL' AS [LEDGER]

      FROM [dbo].[SALFLDGASL] [SALFLDGASL]

      UNION

      SELECT *, 'ASL' AS [COMPANY], 'BUDGET' AS [LEDGER]

      FROM [dbo].[SADFLDGASL] [SADFLDGASL]

       

      The above is working fine but I would now like to join another table called SSRFACC to the above based on the following conditions:

       

      COMPANY field = SSRFACC.SUN_DB

      ACCNT_CODE field = SSRFACC.ACCNT_CODE

       

      I am unsure how to group link the fields because of my union query unless I have to group all those tables together and reference them as one table.

       

      Anyway hope someone can help with this.  I though it a simple join but looks more complicated to achieve in Tableau.

       

      Thanks all

        • 1. Re: COMPLEX SQL JOIN
          Alex Kerin

          I would have guessed that you would do the join for each of the unions?

          • 2. Re: COMPLEX SQL JOIN
            Russell Christopher

            The variant of ANSI SQL your RDBMS supports will impact the approach that you take, but essentially you need to do a nested subquery for your UNION and then JOIN the results the 3rd table. Example stolen from the internet which uses different table/field names but will give you the general idea:

             

            SELECT * FROM

            (SELECT Customers.CustomerID, 0 As EmployeeID

            FROM Customers

            UNION

            SELECT Orders.CustomerID, Orders.EmployeeID

            FROM Orders) As E

            INNER JOIN Employees ON E.EmployeeID = Employees.EmployeeID

             

            ...your WHERE clause will look something like WHERE (E.Company = SSRFACC.SUN_DB AND E.ACCNT_CODE = SSRFACC.ACCNT_CODE)

             

            You might also find it easier simply to use blending "blend" the 3rd table (in a distinct data source) to the first two (in the original data source). This assumes you only need to get at measure values in the 3rd table.