AnsweredAssumed Answered

Learning ExecuteSQL in FMP12, a few questions

Question asked by RamakrishnanRajaramSrinivasan on Apr 25, 2014
Latest reply on Apr 25, 2014 by c.wagner1


Learning ExecuteSQL in FMP12, a few questions


     Hello all,

     I have joined a new job where I am required to use FileMaker (and gradually transition systems to other databases). I have been a DB Admin of a MS SQL Server database for ~2 years, and I am very well versed in PL/SQL and T-SQL. I am trying to pan my SQL knowledge to FMP using the ExecuteSQL functionaloty, and I'm kinda running into a lot of small pains :)

     I have 2 tables: Movies and Genres. The relevant columns are:

Movies(MovieId, MovieName, GenreId, Rating)

Genres(GenreId, GenreName)

     I'm trying to find the movie with the highest rating in each genre. The SQL query for this would be:

SELECT M.MovieName

    FROM Movies M INNER JOIN Genres G ON M.GenreId=G.GenreId

    WHERE M.Rating=


         SELECT MAX(Rating) FROM Movies WHERE GenreId = M.GenreId



     I translated this as best as I could to an ExecuteSQL query:

ExecuteSQL ("

SELECT M::MovieName FROM Movies M INNER JOIN Genres G ON M::GenreId=G::GenreId

WHERE M::Rating =

(SELECT MAX(M2::Rating) FROM Movies M2 WHERE M2::GenreId = M::GenreId)

"; "" ; "")


     I set the field type to Text and also ensured values are not stored. But all I see are '?' marks. 


     What am I doing incorrectly here? I'm sorry if it's something really stupid, but I'm new to FMP and any suggestions would be appreciated. 


     Thank you!