Learning ExecuteSQL in FMP12, a few questions
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.