Runner5 Runner5 - 1 year ago 77
SQL Question

Need sql query to pull back data that meets several groups of criteria from same table in one query

I need to write an sql query that will pull back the data that meets several groups of criteria from the same table. The easiest way to describe is to imagine using an SQL "in" clause but instead of the internals of that clause being "or"s joining the parameters you want it to match it is instead an "and".

I attempted to use count to verify the correct amount of data was pulled back for each "in" statement but the count can't always be trusted due to other entries being similar for each column.

A sample table might be this:

id count animal
--- ----- ------
1 5 puppy
1 6 cat
1 6 puppy

So, now I need a query that will pull back all entries with an id of 1 and a count of 5 and 6 and an animal of puppy and cat. I pretty much need to verify the entire path of the table entry to know I want to pull it back. Is there any built in function that can do this? Do I need to use a recursive CTE to dig deep after confirming that one set of criteria is met? Thanks for any help.

Answer Source

If I got it right

with cnt as(
    select id
    from tbl
    where [count] in (5,6) and animal in ('puppy', 'cat')
    group by id
    having count(distinct[count])=2 and count(distinct animal)=2
select id, [count], animal
from tbl 
where id in (select id from cnt);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download