SQL Query to get CustomerID from two DE

Question

I'm looking to write a SQL Query to get the CustomerID from Pot A and Pot B with a distinguish label so I can see that CustomerID 123 comes from A and CustomerID 234 comes from B.

I have the following data extensions.

Main DE

CustomerID(PK) |
123 234

Pot A

CustomerID(PK) | ReminderOn|
123

Pot B

CustomerID(PK) | ReminderOFF|
234

Pot A&B

CustomerID(PK) | ReminderOFF| ReminderOn

The way I thought to do this is to add a new column(ReminderOn, ReminderOFF) in each pot and set the CustomerID as the new column created but it doesn't work, I get 0 records. What am I doing wrong?

SELECT
md.CustomerID,
pa.CustomerID as ReminderOn, 
pb.CustomerID as ReminderOFF

FROM [Main DE] md

JOIN [Pot A] pa
ON md.CustomerID = pa.CustomerID

JOIN [Pot B] pb
ON md.CustomerID = pb.CustomerID

Answer 1

Change every instance of JOIN to LEFT JOIN.

The standard (INNER) JOIN will only return customers that exist in Main DE, Pot A and Pot B. This means that if someone is missing from one of the DEs mentioned here, this person will not get found by this query. In you case with ReminderOn and ReminderOff it sounds unlikely that someone should be in both Pot A and Pot B at the same time.

Left Join wil give you all records from Main DE and extend it by information from Pot A and Pot B.

Read more about join types here: SQL Joins