AnsweredAssumed Answered

ExecuteSQL statement help

Question asked by RobWestergaard on Oct 25, 2016
Latest reply on Oct 26, 2016 by beverly

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.

Outcomes