[SalesForce] Query Inner Joins followed by a Left Join

I have a query that I am running that joins a few other data extensions and filters based on Role and Created_At date.

Also I need to remove subscribers that are in this group that are also in another data extension [Squadlockers].

When I attempt to run an update query, I get the following error:

Query failed during execution. Error: Violation of PRIMARY KEY
constraint. Cannot insert duplicate key in object…

SQL Query:

 SELECT
        Dusr.Contact_Key AS USR_Contact_Key,
        Dusr.Email AS USR_Email,
        Dusr.First_Name AS USR_First_Name,
        Dusr.Last_Name AS USR_Last_Name,
        Dusr.Created_At AS USR_Created_At,
        Dusr.Last_Login_At AS USR_Last_Login_At,
        Dusr.Phone_Number AS USR_Phone_Number,
        UTR.Role as UTR_Role,
        UTR.Season_Team_Id as UTR_Season_Teams_Id,
        sn_tms.Team_Id AS SN_TMS_Team_Id,
        sn_tms.Name AS SN_TMS_Name,
        sn_tms.Is_Current_Season AS SN_TMS_Is_Current_Season,
        sn_tms.Created_At AS SN_TMS_Created_At,
        sn_tms.Updated_At AS SN_TMS_Updated_At,
        sn_tms.Id AS SN_TMS_Id,
        sn_tms.Roster_Count AS SN_TMS_Roster_Count,
        tms.Id as tms_Id,
        tms.Image as tms_Image



    FROM 
    [Deliverable_Users] Dusr
    INNER JOIN ENT.[User_Team_Roles] UTR ON Dusr.Contact_Key = UTR.Contact_Key
    INNER JOIN ENT.[Season_Teams] sn_tms ON UTR.Season_Team_Id = sn_tms.Id
    INNER JOIN ENT.[Teams] tms ON sn_tms.Team_Id = tms.Id
    LEFT JOIN ENT.[Squadlockers] sl ON Dusr.Contact_Key = sl.Contact_Key

    WHERE UTR.Role LIKE 'Coach'
    AND sn_tms.Created_At >= DATEADD(day, -120, cast(GETDATE() AS DATE))
    AND sl.Contact_Key IS NULL

Here's the DE depiction:

USERSSquadlockers

Best Answer

The issue is that your current query is trying to insert more than one USR_Contact_Key in to the Target DE.

Try this :

SELECT x.*

FROM 

(SELECT
        Dusr.Contact_Key AS USR_Contact_Key, /* Primary Key */
        row_number()over(partition by Dusr.Contact_Key order by Dusr.Contact_Key DESC) AS row,
        Dusr.Email AS USR_Email,
        Dusr.First_Name AS USR_First_Name,
        Dusr.Last_Name AS USR_Last_Name,
        Dusr.Created_At AS USR_Created_At,
        Dusr.Last_Login_At AS USR_Last_Login_At,
        Dusr.Phone_Number AS USR_Phone_Number,
        UTR.Role as UTR_Role,
        UTR.Season_Team_Id as UTR_Season_Teams_Id,
        sn_tms.Team_Id AS SN_TMS_Team_Id,
        sn_tms.Name AS SN_TMS_Name,
        sn_tms.Is_Current_Season AS SN_TMS_Is_Current_Season,
        sn_tms.Created_At AS SN_TMS_Created_At,
        sn_tms.Updated_At AS SN_TMS_Updated_At,
        sn_tms.Id AS SN_TMS_Id,
        sn_tms.Roster_Count AS SN_TMS_Roster_Count,
        tms.Id as tms_Id,
        tms.Image as tms_Image



    FROM 
    [Deliverable_Users] Dusr
    INNER JOIN ENT.[User_Team_Roles] UTR ON Dusr.Contact_Key = UTR.Contact_Key
    INNER JOIN ENT.[Season_Teams] sn_tms ON UTR.Season_Team_Id = sn_tms.Id
    INNER JOIN ENT.[Teams] tms ON sn_tms.Team_Id = tms.Id
    LEFT JOIN ENT.[Squadlockers] sl ON Dusr.Contact_Key = sl.Contact_Key

    WHERE UTR.Role LIKE 'Coach'
    AND sn_tms.Created_At >= DATEADD(day, -120, cast(GETDATE() AS DATE))
    AND sl.Contact_Key IS NULL) x

WHERE x.row = 1

Should get rid of your error.

Related Topic