Correlated sub query


Here is one of my current challenges around the correlated sub query. As per your past answer it can only be used with KEYS. But how would i solve the following issue as it is not really a join. I have a query which selects all documents tracked for a client. like this
(Simplified example)
Select doc_name, status, tracking_nbr from Contacts where _type = "track_request" and contact_id = "1234"

This returns me a list just fine, now in that grid i also want to show when there was any activity on this doc for which i need to get the newest date for this trcking_nbr in my tracker docs. So i would use something like

Select event_date from Contacts where _type="tracker" and tracking_nbr = "ABCD1234" order by event_date DESC Limit 1

So the question is how can i integrate these 2 query’s to return the last activity for the doc in my result ? Or do i have to go and take first result, loop thru it and get the date and then push it into my result before returning it ?

SELECT tr, MAX(t.event_date) AS event_date
FROM Contacts AS tr
JOIN Contacts AS t ON t._type="tracker" AND t.tracking_nbr = tr.tracking_nbr
WHERE tr._type = "track_request" AND tr.contact_id = "1234"

Is there any plans to support none correlated sub queries which don’t use keys in the not so far distance ?


You can track this here MB-30813