ExecuteSQL - Join tables

[aguest]

HI,

 

I'm attempting to join two tables in my Select statement for my executeSql function. All I'm getting back is '?' although if I select from each table seperately I get restults

I know the select statement is correct as I've tested it seperately (outside of filemaker) with a sample of the data.

Is FM capable of working through semi complicated SQL queries? The help seems to suggest it can deal with joins!

This is my script and as you'll see it's not very complicated at all.

 

ExecuteSQL(
"SELECT b.TermDesc, a.ObsDate, a.Grade
 FROM TutorObservations a
 INNER JOIN SchoolYear b on (a.ObsDate ≥ b.StartDate) and (a.ObsDate ≤ b.EndDate)
 ";
"|"; "¶"

)

TIA

[eos]

Try using proper (non-FM style) operators:

ExecuteSQL ( "
  SELECT S.TermDesc, T.ObsDate, T.Grade
  FROM TutorObservations T
  JOIN SchoolYear S
    ON T.ObsDate >= S.StartDate AND T.ObsDate <= S.EndDate
  " ; "|" ; ""
)

Edited July 14, 2015 by eos

[aguest]

Thank you. I missed that in all the attempts to figure it out.