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

Question

So, I total 4 data extensions.

I have records in 3 data extension and I want to save result in data extension# 4.

Here is a sample of how my data extensions looks like:

Desired result: CustID is the key that exists in all data extension (just by a slightly different column name). I need to find out, ALL records from DataExtension_1 that exists in DataExtension_2 with CustType = 'Student' and Status = 'TRUE' but who do not exists in DataExtension_3 with TravelDate as 13 months or less.

According to this requirement, ID# 3 record should be in the results.

This is what I have created so far:

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'
INNER JOIN DataExtension_3 AS c
    ON a.CustID = c.Cust--ID
WHERE c.Cust--ID IS NULL AND c.TravelDate < DATEADD(month, -13, GETDATE()

I am getting the following error:

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

Answer 1

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