1

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:

enter image description here

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'.

5

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:

  • Thank you @Markus. The update did fix the syntax error but I am not getting any results? Since I have provided my DataExtension layout and desired output, could you please advise on the usage of query? – kuml Aug 16 '17 at 16:01
  • Do you want all entries not in data extension 3 or all matches that have a traveldate at least 13 months ago? Your query fails as I guess there are no null values allowed and present in c.Cust--ID. Additionally an inner join does only give results that are present in both joined tables. – Markus Slabina Aug 16 '17 at 16:05
  • I want 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. I guess, when I am stating NULL condition then it is returning 0 since I can either go with NULL or TravelDate, correct? Can you please show me an example of how I will get all entried not in data extension 3 VS all matches travel date at least 13? Just so that I can understand. – kuml Aug 16 '17 at 16:37
  • If I now understood correctly you just want to filter using the third data extension. If that is the case, try the query in my updated answer. – Markus Slabina Aug 16 '17 at 17:05
  • you're missing a right paren in the where clause – Adam Spriggs Aug 16 '17 at 17:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.