hi,
I have a question about nesting multiple query tables into one. I followed the manual for the 3 table configuration and it works with 2 tables Notes and users. But now i want to add the 3rth table adding the possibility to only see the comments on that motor but it won't show me the data. is there something i'm doing wrong?
SELECT itemNotes.noteId AS id,
Users.username AS whoID,
Notes.tStamp,
Notes.note,
Notes.filename,
Notes.sticky
FROM Notes
INNER JOIN Users ON Notes.whoID = Users.id
INNER JOIN itemNotes ON Notes.id = itemNotes.noteId
WHERE itemNotes.accountId = 'Motor 1'
ORDER BY Notes.tStamp DESC
this is a picture of the data
this is a picture of what is inside the itemNote query
Show how {Root Container.MotorMessagePanel.pathID} is defined.
I would convert this to a named query, then feed {Root Container.MotorMessagePanel.pathID} as a parameter to the NQ. When you do straight SQL bindings and have to do your own quotations of things like strings and dates, it's very easy to mess it up or to have an inner " or ' ruin it. Name Queries avoid this and format strings/dates for you.
my {Root Container.MotorMessagePanel.pathID} is just to the UDT of the motor that i know which motor it is. Not so important. the id in itemNotes is the same as the name in that pathID to look up the values but it still wont show me even with a named query. :idPanel is now parsing the value. still won't show me anything
SELECT itemNotes.noteId AS id,
Users.username AS whoID,
Notes.tStamp,
Notes.note,
Notes.filename,
Notes.sticky
FROM Notes
INNER JOIN Users ON Notes.whoID = Users.id
INNER JOIN itemNotes ON Notes.id = itemNotes.noteId
WHERE itemNotes.accountId = :idPanel
ORDER BY Notes.tStamp DESC
I see you are doing INNER joins. If there is no valid User.id to the Notes.whoID (like maybe you have a NULL), you won't see anything.
Change the INNER to LEFT joins temporarily and re-reun the query and post the results. It should show why the INNER is filtering data on you.
You may also just want to do a select * temporarily as well or add any columns that are used in your joins (both sides of the ON statement) to the SELECT clause.
