Need help with the query for update

Question

Here is a screenshot of my Data Extensions

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?

Answer 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())