aguest Posted September 3, 2015 Posted September 3, 2015 I've searched on the forum and help and can't see if this is something that can be done or not. Any help would be good. I've got the following script as an example where I want to use a subquery to summarise information / choose the top ten etc.The inner query runs perfectly but when I wrap the additional SQL around it I get a "?" as the output. Can Filemaker deal with subqueries like this or is there a way of getting round it that I'm missing? TIA ExecuteSQL("SELECT a.FullName, a.TIncidents FROM (Select b.FullName, Count(*) as TIncidents From BullyingDetails a JOIN bullying_VICTIM b on a.fk_RaisedBy = b.pk Group BY b.FullName ) a ORDER BY TIncidents DESC";"|"; "¶" )
eos Posted September 3, 2015 Posted September 3, 2015 You can use sub-queries, but not this way: FROM expects a reference to table (occurrence); what your sub-query returns is (if any) a list of results. What are you trying to return as a result?
aguest Posted September 3, 2015 Author Posted September 3, 2015 Thanks, How does Filemaker deal with sub queries then as this would be good to know for future reference? What I'm trying to return in this instance above is the number of incidents raised by students, which the inner query does but I'd like to limit it to just the top 10 ordering it by the TIncidents. TIA ExecuteSQL("SELECT top 10 a.FullName, a.TIncidents FROM (Select b.FullName, Count(*) as TIncidents From BullyingDetails a JOIN bullying_VICTIM b on a.fk_RaisedBy = b.pk Group BY b.FullName ) a ORDER BY TIncidents DESC";"|"; "¶" )
eos Posted September 3, 2015 Posted September 3, 2015 Try something along the lines of ExecuteSQL ( " SELECT v.FullName, b.TIncidents FROM BullyingDetails b JOIN bullying_VICTIM v on b.fk_RaisedBy = v.pk // allows the use of FullName WHERE TIncidents IN ( SELECT b2.TIncidents FROM BullyingDetails b2 GROUP BY b2.TIncidents ORDER BY ( SELECT COUNT (*) FROM BullyingDetails b3 WHERE b2.TIncidents = b2.TIncidents ) DESC FETCH FIRST 10 ROWS ONLY ) // this restricts the found records to Top 10 incident types ORDER BY v.FullName GROUP BY v.FullName "; "|"; "¶" ) which has a sub-sub-query.
Recommended Posts
This topic is 3466 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now