SQL Inner join

[mjaanes]

Does anybody know if the SQL language in LUA scripts for LM5 support inner joins? If "yes", where can I find the syntax?

Example: I want to read all entries from the object log for all objects having a tag called (say) 'MYTAG'

I have already built a script that first uses grp.tag to create a table with all objects and then I loop through each entry in the table and use SQL SELECT from the objectfile. 
This works fine, but it would be more elegant to do it all in one SQL SELECT, joining the tables "objects", "objecttags" and "objectlog"  (via objects.id, objecttags.object and objectlog.address)

Thanks

[admin]

You can use JOIN syntax. It's pretty much standard SQL, see this for complete syntax: https://sqlite.org/lang.html

[mjaanes]

Thanks so much. This syntax document was extremely helpful.
Posting the resulting SELECT statement for inspiration

Example: Read all entries from the object log occurring after a certain logtime and only for all objects having a certain tag

This did the trick:

*********** START CODE ****************

query = [[
SELECT objectlog.src, objectlog.address, objectlog.datahex, objectlog.logtime, objectlog.eventtype
FROM objectlog
JOIN objects ON objectlog.address=objects.address
JOIN objecttags ON objects.address=objecttags.object
WHERE objecttags.tag=? AND objectlog.logtime>?
ORDER BY objectlog.id ASC
]]

-- Remember to have initiated the sCurrTag and iLastTime variables with the specific values
for _, row in ipairs(db:getall(query,sCurrTag, iLastTime)) do
...do stuff with row....
end

*********** END CODE ****************