[SalesForce] SQL Query: Data view with data extension

I am trying to make an SQL query from a data extension with a data view, but i can't get it to work. 

SELECT 
a.id as Id
, a.email as email
, a.FirstName as FirstName

from contact_salesforce a

left join _Open o
on o.SubscriberKey = a.id

it gives an error when i do the automation:

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

Data Extension fields:

Id Text 18

Email EmailAddress 254

FirstName Text 40

Best Answer

The query specified will return multiple rows if more than one open event is found for the particular subscriber. You can add the keyword distinct to your query to insure only unique row of data are returned by your query. This will eliminate the key violation.

SELECT distinct
a.id as Id
, a.email as email
, a.FirstName as FirstName

from contact_salesforce a

left join _Open o
on o.SubscriberKey = a.id

Normally, I avoid using the distinct keyword and opt for aggregating the query results in some way. But since this query is only selecting columns from the contact_salesforce data extension, using distinct will work in this case.

Related Topic