6 Replies Latest reply on Sep 23, 2016 1:45 PM by Mike Loveless

    Filter on a Left Join

    Mike Loveless

      Hi all, fairly basic question but from my search I wasn't able to find anything.  I am looking to move away from custom SQL queries and using the tool that Tableau provides.  However, I can't figure out how to do joins within Tableau that aren't the most basic.  The example that I'm trying to replicate would be Joining Table A and B (within Tableau instead of SQL) that would produce similar to the below query example. 

       

      SELECT *

      FROM table_a a

      LEFT JOIN table_b b ON b.id = a.id AND b.field1 IS TRUE

       

      Still want data from Table_a if there is no data in table_b where b.field1 IS TRUE which means I wouldn't be able to use b.field1 IS TRUE as a filter.  I can't add a workbook, but below is a quick example of what I'm trying to achieve, assuming both are complete tables.

       

       

      Thanks!

        • 1. Re: Filter on a Left Join
          Benjamin Greene

          I am by no means a SQL expert, but try changing the last bit to WHERE b.field1='TRUE' instead of AND b.field1 IS TRUE

          • 2. Re: Filter on a Left Join
            Mike Loveless

            Benjamin, if I did that I'd be excluding the results from TableA that I wanted.  I'd be getting something like below as 3, 4, and 5 don't have anything in Table B so it doesn't meet the requirements of the filter.  To your point, there is another way to accomplish this by adding an OR b.field1 IS NULL.  I haven't been able to figure out OR statements either. 

             

            • 3. Re: Filter on a Left Join
              Alex Xu

              Hi Mike,

              what about:

              SELECT *

              FROM table_a a

              LEFT JOIN

              (

              select *

              from table_b b

              where b.field1 = 'TRUE'

              ) b_table

              on b_table.id = a.id

              • 4. Re: Filter on a Left Join
                Mike Loveless

                I think I need to edit my initial question to clarify.  I could do it as a custom sql query, I'm trying to do the Joins within Tableau as opposed to doing a custom SQL query.  I've had to do the custom sql queries to pull the data into tableau for every dataset I've pulled in because of CTEs, casting, and other things where I couldn't figure out how to do it in Tableau. 

                 

                Having said that, Alex, that does appear like it would work.  Unfortunately I'd call it another way to ask the question, not the type of answer I was looking for. 

                • 5. Re: Filter on a Left Join
                  Alex Xu

                  I see. Sorry for misunderstanding.

                  How about this? You left join using ID. and in the filter you exclude the "false" and keep "true and null".  Hope i understood correctly this time:)

                  • 6. Re: Filter on a Left Join
                    Mike Loveless

                    Many thanks!  That appears to be exactly what I was looking for.  I'll have to play with it a bit but at least know where to look now!  I had a feeling it was a simple one as it seems like it would be a pretty common problem.