NOT EXISTS

[sal88]

Hi all

I'm trying to utilise the NOT EXISTS command:

I need to find records in the Cases table that do not have matching records in the Originals table.

So far I have:

SELECT a.Case_ID
FROM Cases a
WHERE a.Closed IS NULL 
AND
NOT EXISTS
    (SELECT * 
     FROM Originals b
     WHERE b.Case_IDF = a.Case_ID)

            
It just freezes when I try this though. Could it be that because there are a lot of records in each table? I will be using additional criteria - not sure if this would improve performance if this is the case.

Any help appreciated.

Thanks

[Wim Decorte]

Most likely it is because "NOT EXISTS" is not supported in FM's SQL syntax.  Check the list of supported functions and keywords.

[beverly]

I found a sample of "EXISTS" in ExecuteSQL here (the example file from SkeletonKey):

http://www.skeletonkey.com/executesql-function-filemaker-12-web-series-follow-up-q-a/

These links may also be helpful:

http://www.w3resource.com/sql/special-operators/sql_exists.php

 

https://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html

 

http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

 

beverly

 

[Josh Ormond]

It looks, if I am reading this correctly, that you may just have a syntax problem. Each of the examples I've seen, EXISTS is only used directly after a where clause.

[sal88]

Have got a script trigger to set a flag field in the parent table if no records existing in the child table, then just use a simpler SQL command. It took far too long to do it all in SQL.

Cheers guys