I have a simple table with Id and groupdId.
There isnt a unique index.
Id GroupId
3 4
3 118
4 3
4 57
4 122
Like that....
I need to be able to select when a contact
has ALL of these groups
has ANY of these groups
DOES NOT have ANY of these groups
DOES NOT have ALL of these groups
but nested
an is example:
I need all contacts that have groups 142 and 147 and 115, BUT do not have 113 and 187.
I am able to get ANY / ANY to work ALL / ANY to work
but I cannot get ANY / ALL or ALL / ALL
This works:
SELECT Id
FROM i_groups
WHERE Id IN (
SELECT Id FROM i_groups WHERE groupId NOT IN ('113')
)
AND groupId IN ('107' ,'111' ,'165' )
GROUP BY Id HAVING COUNT(*) = 3
This does not work:
SELECT Id
FROM i_groups
WHERE Id IN (
SELECT Id FROM i_groups WHERE groupId NOT IN ('113','115') GROUP BY HAVING COUNT() = 2
)
AND groupId IN ('107' ,'111' ,'165' )
GROUP BY Id HAVING COUNT() = 3
When I add the GROUP BY to the end of the nested it breaks the query. No errors.
The query just never completes.
The GROUP BY HAVING COUNT(*) = 2 get me all contacts who have ALL of those tags /
or ALL contacts who DO NOT have ALL those tags. Without it, I get ANY not ALL.
Thanks All...
