Violation of PRIMARY KEY constraint. Cannot insert duplicate key

Question

Getting error on the query and is below.

Email_Master primary key is 'Email Address' and thee target Data Extension primary key is also 'Email Address'. Can you help/advise what is the issue on the query statement?

ERROR: Query failed during execution. Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object 'In Email_Master _subscribers Status'

SELECT 
a.[Email Address] as 'Email Address', 
a.[Subscriber Key] as 'Subscriber Key', 
b.status as 'Engagement',
b.SubscriberID,
b.DateUndeliverable,
b.DateUnsubscribed,
b.DateJoined,
b.BounceCount,
b.SubscriberType,
b.Locale,
b.Domain
FROM [Email_Master] as a
JOIN [_Subscribers] as b
ON a.[Email Address] = b.[EmailAddress]

Answer 1

Your join is resulting for multiple subscribers from _Subscribers, so you'll need to handle that case and pick only one of the duplicates to return.

I'd handle it with a windowing function:

select 
  x.[Email Address] 
, x.[Subscriber Key] 
, x.Engagement 
, x.SubscriberID
, x.DateUndeliverable
, x.DateUnsubscribed
, x.DateJoined
, x.BounceCount
, x.SubscriberType
, x.Locale
, x.Domain
from (
    SELECT 
      a.[Email Address]
    , a.[Subscriber Key]
    , b.status as 'Engagement'
    , b.SubscriberID
    , b.DateUndeliverable
    , b.DateUnsubscribed
    , b.DateJoined
    , b.BounceCount
    , b.SubscriberType
    , b.Locale
    , b.Domain
    , row_number() over (partition by a.[email address] order by b.dateJoined desc) rowNum
    FROM [Email_Master] as a
    JOIN [_Subscribers] as b ON a.[Email Address] = b.[EmailAddress]
) x
where x.rowNum= 1

The innermost query assigned a number for each of the rows by email address, the order of the duplicates is specified by the order by clause. The outermost query just picks those that have a rowNum of 1.