10 Replies Latest reply on Oct 26, 2016 2:14 PM by beverly

    ExecuteSQL statement help

    RobWestergaard

      I have two tables, Jobs and Leads, related on Jobs::ID = Leads::JobID. A related Leads record may or may not exist, and may or may not have a value in a field; there will never be more than one related Leads record. I need to count how many Job records have a specific value in a field and do not have a specific value in the Leads field. For my purposes, Jobs records where no Leads record exists should be included in my count.

       

      ExecuteSQL (

      "SELECT COUNT(a.\"ID\") FROM \"Jobs\" a

      INNER JOIN \"Leads\" b ON a.\"ID\" = b.\"JobID\"

      WHERE a.\"Field1\" = ? AND b.\Field2\" <> ?" ;

      Char ( 9 ) ; "¶" ; "desiredValue" ; "valueToAvoid" )

       

      The above statement is not counting records in Jobs where the related Leads record does not exist at all. Is there a SQL statement that will?

       

      I'm grateful for any help.