This forum uses cookies
This forum makes use of cookies to store your login information if you are registered, and your last visit if you are not. Cookies are small text documents stored on your computer; the cookies set by this forum can only be used on this website and pose no security risk. Cookies on this forum also track the specific topics you have read and when you last read them. Please confirm that you accept these cookies being set.

SQL Inner join
#1
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
Reply
#2
You can use JOIN syntax. It's pretty much standard SQL, see this for complete syntax: https://sqlite.org/lang.html
Reply
#3
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 ****************
Reply


Forum Jump: