Analytics, ADQL and a join

[Ashley.Ings]

Is it possible to use a join as part of the SQL to pull back some info collect by AppD? 

The problem is I have 3 parameters in AppD, username, connection-status and eventtime. Each user could be in a state of ‘connected’ or ‘disconnected’ so I want to pull back the information and then max the eventtime so it pulls back the most up to date connection status. I would normally do this in SQL with a join. I’ve tried to add a table reference to the ‘transaction’ table as t1 and then add the t1 to each of the fields even before I add the join, but I don’t think it support join / subqueries

Examples code

SELECT segments.userData.userName,
segments.userData.brand,
segments.userData.status
FROM transactions tt1
INNER JOIN (SELECT segments.userData.userName, MAX(eventdate) AS MaxDateTime FROM transaction GROUP BY segments.userData.userName) tt2 ON tt1.segments.userData.userName = tt2.segments.userData.userName AND tt1.eventdate = tt2.MaxDateTime
WHERE tt1.application = "aaa"
AND tt2.transactionName = "tttt"
AND tt2.segments.userData.status = "Connected"