Jump to content

Sub Query within ExecuteSQL


This topic is 3093 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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
";
"|"; "¶" )

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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
";
"|"; "¶" )

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 3093 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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.