sql - Counting amount of values in joined table with multiple corresponding entries -

trying figure out following sql command:

you have 2 tables, maintable , subtable. maintable consists of pkmaintable column , subtable consists of pksubtable, fkmaintable, bitval1, bitval2

i want figure out every entry in maintable, in corresponding (-> m.pkmaintable = s.fkmaintable) subtable entries none have (bitval1 = true or bitval2 = true)

my current attempt (in pseudo-sql code, doesn't work):

select * maintable m     inner join subtable s on m.pkmaintable = s.fkmaintable sum(case when s.bitval1 = 1 1 else 0 end) = 0    or sum(case when s.bitval2 = 1 1 else 0 end) = 0 

example tables:

maintable

pkmaintable<br> 1<br> 2<br> 3<br> 4<br> 

subtable

pksubtable | fkmaintable | bitval1 | bitval2<br> 1          | 1           | 1       | 1<br> 2          | 1           | 0       | 0<br> 3          | 1           | 0       | 1<br> 4          | 2           | 0       | 0<br> 5          | 2           | 0       | 1<br> 6          | 3           | 0       | 0<br> 7          | 3           | 1       | 0<br> 8          | 3           | 0       | 1<br> 9          | 4           | 0       | 0<br> 

the result should give entries in maintable pkmaintable = 2 or 4

for conditions on aggregate functions (sum, count, etc.) need use having clause. in case overcomplicate things, not exists function should work fine - returns true if subquery returns 0 rows. example:

select *  maintable m not exists (select *  subtable s m.pkmaintable = s.fkmaintable , (s.bitval1 = 1  or s.bitval2 = 1)) 

---

and here how aggregate function:

select m.pkmaintable maintable m inner join subtable s on m.pkmaintable = s.fkmaintable group m.pkmaintable having sum(case when s.bitval1 = 1 1 else 0 end) = 0      or sum(case when s.bitval2 = 1 1 else 0 end) = 0