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
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 ?