Having created a database with LITA, my query is not returning any data and I can't work out why.. my code is listed below:
var stmt:SQLStatement=new SQLStatement();
stmt.text="SELECT templates.name AS 'template', entries.name, fields.label, data.value, types.type FROM data INNER JOIN entries ON data.entriesid = entries.id INNER JOIN fields ON fields.id = data.fieldsid INNER JOIN templates ON fields.templatesid = templates.id INNER JOIN types ON fields.typesid = types.id WHERE data.entriesid = :idParam";
//stmt.text="SELECT * FROM entries WHERE id=:idParam";
The SQL works perfectly in LITA and the comment simple commented statement works fine, but with the full statement, the result.data is null.
Can anyone see anything obviously wrong or point me in the right direction for debugging this.
What if you move the joins to be part of the where clause?
SELECT templates.name AS 'template', entries.name, fields.label, data.value, types.type FROM data,entries,fields,templates,types WHERE data.entriesid = entries.id AND fields.id = data.fieldsid AND fields.templatesid = templates.id AND fields.typesid = types.id AND data.entriesid = :idParam
Hey, thanks for your post.
My preference is always to use real joins in SQL statements - they are faster in most DB engines.
That said, I did try to reformat the statement, but still got the same result.
I then picked it apart 1 join at a time and realised that I hadn't populated the 'fields' table - DOH!
I look forward to the day when foreign key enforcement is in place (come on Adobe, please upgrade sqlite) - it's very difficult to work with complex data structures without it.