CODEWITHSUNDEEP
sqlpostgresql
Tarlen
Tarlen

Reputation: 3797

NOT EXIST in correalted subquery returns too many rows

I want to select records for which N number of conditions matches an associated table.

I currently try to accomplish like this

select v.id, name
from visitors v
left join trackings t on t.visitor_id = v.id
where 
    v.app_id = 'A0I' 
    and (
        (NOT EXISTS (
            SELECT v.id
            FROM trackings not_t 
            WHERE v.id = not_t.visitor_id and field = 'admin'
        ))
        or (t.field = 'app_name' and t.string_value ILIKE 'gitchecker')
        or (t.field = 'users_created' and t.integer_value > 0)
    )
    group by v.id
    having count(*) = 3 -- <number of conditions>

Which works fine, unless I try to express the 'unknown' condition through the NOT EXISTS subquery. This subquery returns too many rows, as it does not seem to be filtered on the v.id = not_t.visitor_id

Any ideas?

Upvotes: 2

Views: 56

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

I'm not sure why your version is not working. It might be the HAVING clause which should be 2 or 3 depending on the match.

But, why not phrase the logic like this?

select v.id, name
from visitors v join
     trackings t 
     on t.visitor_id = v.id
where v.app_id = 'A0I' 
group by v.id, name
having sum( (t.field = 'app_name' and t.string_value ILIKE 'gitchecker')::int) > 0) and
       sum( (t.field = 'users_created' and t.integer_value > 0)::int) > 0 and
       sum( (t.field = 'admin')::int) = 0;

Note that the left join is unnecessary because your conditions require matches.

I find that for these types of queries -- set-with-in-set queries -- group by and having is the most versatile way to express most conditions.

Upvotes: 1

Related Questions