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.
"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.