Left Join in Statistica Query - Statistica General Discussion - Statistica - Dell Community

Left Join in Statistica Query

Left Join in Statistica Query

This question is answered

I would like to join a few tables together using the basic inner join functionality provided in Statistica but then left join one additional column to those and do it in a single query.  I can perform the multiple table inner join and the single left join separately, but can't figure out how to perform them together.  If someone could show me the code for a successful inner join of 3 tables with a 4th table left joined to it, I would greatly appreciate it.

Verified Answer
  • AdvancedQueryBuilder_worksapce.zip

    Hi John, 

    Here below are example codes to Inner Join 3 tables: Cyclone1, Cyclone2, Cyclone3 and Left join a 4th table Cyclone 4 by the common variable ID:

    SELECT Cyclone1."ID"
                   , Cyclone1."Date Time"
                   , Cyclone2."ID"
                   , Cyclone2."Date Time"
    FROM "wx01"."dbo"."Cyclone1"
    INNER JOIN "wx01"."dbo"."Cyclone2"
                ON Cyclone1."ID"=Cyclone2."ID"
    INNER JOIN "wx01"."dbo"."Cyclone3"
                ON Cyclone2."ID"=Cyclone3."ID"
    LEFT JOIN "wx01"."dbo"."Cyclone4"
                ON Cyclone3."ID"=Cyclone4."ID"

    Here is a KB article about how to access external data. At Step 10, you can switch to text mode to write your scripts for building a query.

    In addition, Statistica advanced query builder is a very comprehensive graphical query wizard which allows you to graphically create advanced queries but also to type in any queries.

    Here below is a screenshot for an advanced query builder example, the default join is an Inner Join, To make it a Left or Right join, click on the connecting line between any two tables. The workspace can be downloaded on the top of the post.

All Replies
  • AdvancedQueryBuilder_worksapce.zip

    Hi John, 

    Here below are example codes to Inner Join 3 tables: Cyclone1, Cyclone2, Cyclone3 and Left join a 4th table Cyclone 4 by the common variable ID:

    SELECT Cyclone1."ID"
                   , Cyclone1."Date Time"
                   , Cyclone2."ID"
                   , Cyclone2."Date Time"
    FROM "wx01"."dbo"."Cyclone1"
    INNER JOIN "wx01"."dbo"."Cyclone2"
                ON Cyclone1."ID"=Cyclone2."ID"
    INNER JOIN "wx01"."dbo"."Cyclone3"
                ON Cyclone2."ID"=Cyclone3."ID"
    LEFT JOIN "wx01"."dbo"."Cyclone4"
                ON Cyclone3."ID"=Cyclone4."ID"

    Here is a KB article about how to access external data. At Step 10, you can switch to text mode to write your scripts for building a query.

    In addition, Statistica advanced query builder is a very comprehensive graphical query wizard which allows you to graphically create advanced queries but also to type in any queries.

    Here below is a screenshot for an advanced query builder example, the default join is an Inner Join, To make it a Left or Right join, click on the connecting line between any two tables. The workspace can be downloaded on the top of the post.