I'm trying to get a query on several fields that are in 2 Data Extension Tables (the matching content is SubscriberKey). I'm familiar with SQL, but I used it primarily creating lists in Oracle's Responsys platform, and had several oddities and order requirements that required a specific methodology to query.
I followed the steps in https://help.marketingcloud.com/en/documentation/exacttarget/interactions/activities/query_activity/ to fit my query. Tried to do it as exact as possible in case formatting is fickle.
SELECT
c.[User Status],
b.[Points Used],
a.[Status] as 'Subscription Status',
a.[EmailAddress] as 'Email'
FROM
_subscribers a
INNER JOIN
_UserData Usage b
_Website Information c
ON
a.Subscriber Key = b._SUBSCRIBERKEY = c._SUBSCRIBERKEY
However I get this error when checking the syntax: "_UserData is not a known data extension or system data view. You can only query existing data extensions or system data views."
These are user data lists from our online database. They are stored in a separate folder (not in the root…maybe that's the issue…).
Any tips?
Also, should each inner join be separated or can it be combined like I have it above?
Note: I'm trying to create a send-able list for active people with a certain amount of points to boost engagement of our most active. The data is on 2 different Extension Tables that I need to create a send Data Extension. Doesn't seem you can use Filters for 2 different data sets even when they have a linking, so my workaround is using SQL to pull the data and create a list.
The plan is to have all this data into 1 table, but that isn't ready for at least a month. Right now data is split into several Data Extensions.
Best Answer
The underscore (_) before the Data Extension name only applies to system-level 'Data Extensions' known as 'Data Views', such as _Subscribers, _Open, _Click, etc. For any of the Data Extensions you created in the account, you will only need the name of the Data Extension - no underscore required. Also, Data Extensions with spaces in their name should be surrounded by square brackets [ ].
With that in mind, your INNER JOIN is still a bit off. Since you are technically joining three tables (subscribers, usage, information), you will need two INNER JOINS: A -> B and B -> C. So it would look a bit more like:
FROM _subscribers a
INNER JOIN [UserData Usage] b
ON a.SubscriberKey = b.Subscriber Key
INNER JOIN
[Website Information] c
ON b.SubscriberKey = c.SubscriberKey
Lastly - relating multiple Data Extensions together through the data filters is possible when using Primary Keys and Data Relationships. Feel free to have a read through this:
https://help.marketingcloud.com/en/documentation/exacttarget/subscribers/data_extensions_and_data_relationships/data_relationships/
Lastly lastly, there aren't too many ExactTarget-specific SQL examples on this website, but I find just searching the regular Stack Exchange for general SQL help can get you through most problems.
Good luck!