0

Here is a screenshot of my Data Extensions

enter image description here

This is the query I am using (it is working fine and giving desired result in my 4th Data Extension which is my target DE for query.

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 TravelDate <= DATEADD(month, -13, GETDATE())  

             )

I have two questions regarding this:

  1. In the data extension where I am saving the output of the query that we discussed. If I want to include "TravelDate" column data also which is in DataExtension3. How the SQL script will change?

  2. Every time when I run this query, there is a chance that my query may bring ADDITIONAL records and encounter records that already exists in my target data extension, and I do not want duplicates. Technically I can't have duplicated in my target DE because I have the subscriberKey as primary key.

I have "Update" selected as query type but it is not adding new records in my target DE. Basically nothing happens :(

I have read the HELP DOC and according to doc, UPDATE type should update records in the data extension with query results and appends non-matching records.

I don't see UPDATE or Append happening with the above query?

-1

This will add in travel data and will give you only records in all three data extensions, ie cust 1 and cust 3. If you want all the records, you need to not inner join.

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

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.