Error: Unable to create temporary data extension: A field named VersionID already exists on object QueryStudioResults

Question

I'm trying to make a select that returns the journeys where a certain user is inserted, but I'm getting this error, does anyone know why?

Select Distinct J.JourneyId, J.JourneyName, S.SubscriberID,SUB.SubscriberID
FROM _Journey J
INNER JOIN _JourneyActivity JA ON JA.VersionID = J.VersionID 
INNER JOIN _Sent S ON JA.JourneyActivityObjectID = S.TriggererSendDefinitionObjectID
INNER JOIN _Subscribers k ON S.SubscriberID = k.SubscriberID
WHERE SUB.SubscriberID='0016g00001AEHcyAAH'

Answer 1

1. The alias "SUB" is not in your code. It probably was at some point, but was since replaced with "k", which is _Subscribers as per your code.

2. Your code as shared above now returns the error that "subscriberId" is already in the target DE; That makes sense, you have to remove it once from the SELECT statement.

This will give you the following query:

SELECT DISTINCT
J.JourneyId
,J.JourneyName
,S.SubscriberID

FROM _Journey J
INNER JOIN _JourneyActivity JA 
ON JA.VersionID = J.VersionID

INNER JOIN _Sent S
ON JA.JourneyActivityObjectID = S.TriggererSendDefinitionObjectID

INNER JOIN _Subscribers k 
ON S.SubscriberID = k.SubscriberID

WHERE k.SubscriberID = '0016g0000XXXXXXX'

3. Next issue: you are mistaking SubscriberId and SubscriberKey. SubscriberID is a number field that cannot contain Salesforce IDs (which contain letters) but always contains a marketing Cloud generated number.

So likely, replace subscriberId with subscriberKey everywhere:

SELECT DISTINCT
J.JourneyId
,J.JourneyName
,S.subscriberKey

FROM _Journey J
INNER JOIN _JourneyActivity JA 
ON JA.VersionID = J.VersionID

INNER JOIN _Sent S
ON JA.JourneyActivityObjectID = S.TriggererSendDefinitionObjectID

INNER JOIN _Subscribers k 
ON S.subscriberKey = k.subscriberKey

WHERE k.subscriberKey = '0016g0000XXXXXXX'

This should be functional (if you reinsert the correct ID again, it will also return something other than 0 :) )

==

For further reading, but way outside of the scope of this question:

Using the Account ID (001xxxxxxx) as subscriberkey is not recommended as this is not how the MC Connect features understand "persons". You might be using PersonAccounts? In that case - If you can help it, you want to use the personContactId (003xxxxxxx) as subscriberkey throughout, otherwise you run the risk of creating duplicates when Journey Builder uses the latter as Contacts.

check this: Using Account Id from Salesforce as the Subscriber Key in Marketing Cloud?

or that: Clarification on Marketing Cloud Connect and Contact Count