CODEWITHSUNDEEP
sqlsql-server
J. Polfer
J. Polfer

Reputation: 12481

SQL - Return values that meet all criteria

I have a table like this:

Table1
site | value
a | banana
b | banana
c | banana
a | fred
c | quetzal
a | quetzal

Let's say I have a set of rows with site values

a
b
c

How do I find what values in Table1 have all three of a,b, and c for site?

I know that

SELECT value
FROM Table1
WHERE site IN (SELECT site FROM chosen_sites)

Will return all values that exist for at least one of the chosen sites, but how do I select the ones in all three?

Upvotes: 0

Views: 760

Answers (2)

Blorgbeard
Blorgbeard

Reputation: 103467

Off the top of my head, I think something like this:

select t.value
from Table1 t
join chosen_sites s on s.site = t.site
group by t.value
having count(distinct t.site) = (select count(*) from chosen_sites)

Edit: seems to work.

Upvotes: 2

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can cross join all distinct values with sites and left join table1 on to this and check for counts.

select v.value
from table2 t2 --this is your table2 with all sites
cross join (select distinct value from table1) v
left join table1 t1 on t1.site=t2.site and t1.value=v.value
group by v.value
having count(t2.site)=count(t1.site)

Upvotes: 1

Related Questions