"no luck" as in no results returned, the wrong results returned or the infamous question mark that usually means that you have a syntax error in your SQL?
I've never seen a WHERE clause used between join clauses like you have here so that part of the expression looks suspicious to me, but maybe that's just my inexperience with SQL.
If you are getting the question mark, maybe this form would work:SELECT s.lastName, s.firstName, sec.teacherDisplayFROM student AS sJOIN roster AS r ON s.personID = r.personIDJOIN section AS sec ON sec.sectionID = r.sectionID
WHERE s.studentNumber = '37972' AND sec.sectionID = '859456'
I tried several varieties of the SQL command. All give me the ?
Any other ideas?
You might try using SeedCodes free SQL Explorer file to build your query and see if it works for you. If it does, you can then compare the SQL it produces with yours to try and spot what went wrong in the original.
I guess I didn't reply to your post. I did try your SQL statement as well which resulted in the ? as well.
This SQL Command worksExecuteSQL ("SELECT s.lastName, s.firstNameFROM student AS sWHERE s.studentNumber = '37972'" ;"" ; "" )BUT this does not work (result ?)ExecuteSQL ("SELECT teacherDisplayFROM SectionWHERE sectionID = '859456'" ;"" ; "" )There is a table occurance called SectionThere is a field within the table occurance Section called teacherDisplayThere is a field within the table occurance Section called sectionIDAND there is a record in this table with a sectionID of 859456
You did reply and as I can't spot any obvious errors, I've suggested a different approach that you can try out if interested.
SQL Explorer is a cool program. Just trying it out right now. For whatever reason, SQL is extremely slow. I am trying to run a query from SQL Explorer right now, and it has been over 10 minutes and I am still waiting for the result. I am hoping it works.
Noticed SQL Explorer likes to put parenthesis around all field names and table names (maybe that is the reason mine didn't work???).
I will post when I do find a solution though. Thank you
I think you mean that SQL Explorer double quotes all field and table names using \" fieldname \". Since it's very easy to have names that are either a reserved SQL word or that trip a syntax error such as the one tripped with a field named __pkContactID, it quotes everything to be on the safe side.