[SalesForce] Data extension Inner Join – Incorrect syntax near the keyword ‘WHERE’

I am trying to join 2 data extensions and input the result to a third data extension:

SELECT [Account ID] AS [Account ID], [Created Date Backend] AS [Created Date] 
FROM [DE A]  A
INNER JOIN [DE B] B
WHERE A.[Account ID] = B.[Lead ID]

Currently getting a syntax error:

The query syntax is invalid
Incorrect syntax near the keyword 'WHERE'.

What am I doing wrong?

Best Answer

When conducting a JOIN function, you need to specify what the 2 tables are joining on.

Try this:

SELECT 
[Account ID] AS 'Account ID'
,[Created Date Backend] AS 'Created Date'
FROM [DE A] A
INNER JOIN [DE B] B ON A.[Account ID] = B.[Lead ID]

Note that you may need to specify what table Account ID and Created Date Backend come from.

Reference: https://www.w3schools.com/sql/sql_join.asp

This image will help you understand the basics of JOINs:

Related Solutions

[SalesForce] Trying to find out a record that does not exists in a data extension by query

You can only have one WHERE-clause in your SQL statement, as they are per query rather than per join like in your statement.

So I suggest trying this query (from clarifications in comments and in chat this also includes all the changes necessary to fulfill the asker's requirements):

SELECT a.CustID, a.CustEmail, b.CustType, b.Status
FROM DataExtension_1 AS a 
INNER JOIN DataExtension_2  AS b
    ON a.CustID = b.Cust-ID
WHERE
    b.CustType = 'Student' AND
    b.Status='true' AND
    a.CustID IN
    (
        SELECT Cust--ID
        FROM DataExtension_3
        WHERE c.TravelDate <= DATEADD(month, -13, GETDATE())
    )

Additional information:

Visual Explanation of SQL joins

[SalesForce] use SQL UPDATE Statement in Salesforce Marketing Cloud

No, only SELECT statements are allowed.

You can, however, specify Update, Overwrite or Append as the action on the target Data Extension.

Reference

Marketing Cloud SQL Reference

Best Answer

Related Solutions

[SalesForce] Trying to find out a record that does not exists in a data extension by query

[SalesForce] use SQL UPDATE Statement in Salesforce Marketing Cloud

Related Topic