What is wrong with this query? psql

Question

Objective: to return all records of a given type where the opposite type does not exist.

Problem: No records are returned when I use the Select Count as an 'AND'

SELECT ct.cntrct_id, ar.addr_1, ar.city_nm, ar.st_cd, ar.zip_code, p.phone_nbr, p.frst_nm, p.lst_nm
    FROM addru ad, addrs ar, cntrt ct, persn p, custm cm
    WHERE ad.cntrct_seq = ct.cntrct_seq
    AND ar.addr_seq = ad.addr_seq
    AND p.person_seq = cm.person_seq
    AND cm.clnt_seq = ct.clnt_seq
    AND ad.active_flg = '1'
    AND ad.dist_id = $dist
    AND substring(ct.cntrct_id from 2 for 1) = '$type'
    AND ((SELECT COUNT(*)
            FROM addru ad, addrs ar, cntrt ct, persn p, custm cm
            WHERE ad.cntrct_seq = ct.cntrct_seq
            AND ar.addr_seq = ad.addr_seq
            AND p.person_seq = cm.person_seq
            AND cm.clnt_seq = ct.clnt_seq
            AND ad.active_flg = '1'
            AND ad.dist_id = $dist
            AND substring(ct.cntrct_id from 2 for 1) != '$type') = 0)
    ORDER BY ct.cntrct_id"

Copying the table definitions will take up a lot of space, so if you need to see them, let me know which ones (don't want to bog down the question with too much info) I think my error is a logical one or stems from a misunderstanding of how to form the sql query.

Answer 1

Unless I'm missing something, why can't you do

SELECT ...
FROM ...
WHERE ...
GROUP BY ...
ORDER BY ...
HAVING COUNT(*) = 0

This should be better than running the query twice just so you can do 'inline' count filtering at the row level.