+- Logic Machine Forum (https://forum.logicmachine.net)
+-- Forum: LogicMachine eco-system (https://forum.logicmachine.net/forumdisplay.php?fid=1)
+--- Forum: Scripting (https://forum.logicmachine.net/forumdisplay.php?fid=8)
+--- Thread: SQL Inner join (/showthread.php?tid=2566)
SQL Inner join - mjaanes - 03.04.2020
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
RE: SQL Inner join - admin - 03.04.2020
You can use JOIN syntax. It's pretty much standard SQL, see this for complete syntax: https://sqlite.org/lang.html
RE: SQL Inner join - mjaanes - 04.04.2020
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 ****************