Tarlen Tarlen - 1 year ago 67
SQL Question

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
v.app_id = 'A0I'
and (
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
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?

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download