Sub Query within ExecuteSQL

[aguest]

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]

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]

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]

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.