Message156 Incorect Syntax Near the Keyword 'Join" / SQL Server (and which join?)

Question

   SELECT *
    FROM
        (SELECT *
                FROM  Dim_Placement pl
                 OUTER JOIN 
                 Fact_Media_KPI j1
                 ON pl.SK_Placement_ID = j1.FK_SK_Placement_ID
            ) a1 
             OUTER JOIN
             (SELECT j1.*, pa.Package_Type, pa.Placement_Type, pa.Cost_Method, pa.Package_Name, pa.Package_Start_Date, pa.Package_End_Date,pa.Unit_Amount,pa.Unit_Rate,pa.Unit_Type
                 FROM  Dim_Package pa
                 OUTER JOIN
                 Fact_Media_KPI j1
                 ON pl.SK_Package_ID = j1.FK_SK_Package_ID) a2
                 ON a1.SK_Fact_MEDIAKPI_ID = a2.SK_Fact_MEDIAKPI_ID ) b1

       OUTER JOIN

         (SELECT j1.*, b.Brand_Name, b.Op_Co, b.Category
              FROM  Dim_Brand b
              OUTER JOIN 
              Fact_Media_KPI j1
              ON pl.SK_Brand_ID = j1.FK_SK_Brand_ID
             ) a3
         OUTER JOIN
         (SELECT j1.*, c.Budget_Campaign_Code, c.Campaign_Start_Date, c.Campaign_End_Date, c.Campaign_Status
            FROM  Dim_Campaign c
            OUTER JOIN 
            Fact_Media_KPI j1
            ON pl.SK_Campaign_ID = j1.FK_SK_Campaign_ID
            ) a4
         ON a3.SK_Fact_MEDIAKPI_ID = a4.SK_Fact_MEDIAKPI_ID) b2

    ON b1.SK_Fact_MEDIAKPI_ID = b2.SK_Fact_MEDIAKPI_ID) c1

    OUTER JOIN

        (SELECT j1.*, s.Site_Name
            FROM  Dim_Site s
            OUTER JOIN
            Fact_Media_KPI j1
            ON pl.SK_Site_ID = j1.FK_SK_Site_ID
            ) a5
       OUTER JOIN

       (SELECT j1.*,c.Creative_Adname, c.Creative_Filename, c.Click_Through_URL, c.Creative_ID, c.Creative_Size c.Creative_Type, c.Video_Length
           FROM  Dim_Creative c
           OUTER JOIN
           Fact_Media_KPI j1
           ON pl.SK_Site_ID = j1.FK_SK_Site_ID
           ) a6
        ON a5.SK_Fact_MEDIAKPI_ID = a6.SK_Fact_MEDIAKPI_ID) b3

        OUTER JOIN

        (SELECT j1.*, d.Date
           FROM  Dim_Site s
            OUTER JOIN
            Fact_Media_KPI j1
            ON pl.SK_Date_ID = j1.FK_SK_Date_ID
            ) a7
        ON b3.SK_Fact_MEDIAKPI_ID = a7.SK_Fact_MEDIAKPI_ID) c2
   ON c1.SK_Fact_MEDIAKPI_ID = c2.SK_Fact_MEDIAKPI_ID
   WHERE d.Date >= '2015-01-01';

The spacing has gotten god awful too. What went wrong - with this many Joins, that error is mostly meaningless.

I'm getting an error known as the 156 error - syntax error near a JOIN

One problem - which join?

As far as I can tell, it is legitimate to do nested dependent subqueries. Ideas?

(also, this size, is making it difficult for me to post. Why stack why?)

Missing join-conditions (ON clauses), e.g. just after a5. – jarlh Oct 02 '15 at 13:51 — jarlh, Oct 02 '15 at 13:51

score 2 · Answer 1 · edited May 23 '17 at 11:51

2

You are saying OUTER JOIN only for your JOINs, but you need to specify what type. They can be either RIGHT OUTER JOIN, LEFT OUTER JOIN, or FULL OUTER JOIN.

Please see this post for more information on the different OUTER JOIN types: What is the difference between "INNER JOIN" and "OUTER JOIN"?

edited May 23 '17 at 11:51

Community

1
1

answered Oct 02 '15 at 13:52

Siyual

16,415
8
44
58

1

In this particular case, I suspect a LEFT OUTER JOIN is most likely what is wanted. – HLGEM Oct 02 '15 at 13:59

Message156 Incorect Syntax Near the Keyword 'Join" / SQL Server (and which join?)

1 Answers1