Something like GROUP BY HAVING ALL IN-CodePudding

I want to select a ProductConfig that has exactly the given variants. As it is a many-to-many relationship I have an association table. With the association table I have been trying to use it with the GROUP BY so I can work on the other column.

The problem I am having is that I need an exactly equal operator to a set of values inside the HAVING. Something like HAVING variant_id = (1, 2, 3, 99).

For now I have the following query with some problems

SELECT productconfig_id
FROM association_productconfig_elementvariant
GROUP BY productconfig_id
HAVING variant_id IN (1, 2, 3, 99);

This will match if productconfig_id has variant_id equal to ANY subset of {1, 2 3, 99} like {1, 2} or {1, 3} but I only want it to match with the exact set {1, 2, 3, 99}.

I have another problem the other way around. If productconfig_id has variant_id equal to {1, 2, 50} it will also match because the first to is in the values even though the last is not.

Basically I want to compare equality over a column and a set of values. This second problem will solve if you had something like HAVING ALL IN.

CodePudding user response：

This is probably more on-target with what you need. Here, I am doing both a COUNT() and a sum() based on the matching variant_id in question. This is making sure that whatever records DO qualify the variant get to the 4 count, but ALSO the count() of every variant per configuration.

So, if one product had variants of (1, 2, 3, 5, 12, 99, 102, 150) would have a count(*) = 8, but the specific match = 4 based on those in question.

Now, if you can ignore the overall count of 8, just remove that AND portion from below, but at least you know the primary 4 in consideration are accounted for.

SELECT 
      productconfig_id
   FROM 
      association_productconfig_elementvariant
   GROUP BY 
      productconfig_id
   HAVING 
      sum( case when variant_id in ( 1, 2, 3, 99 )
                then 1 else 0 end ) = 4
      AND count(*) = 4

CodePudding user response：

Could you try this :

Select productconfig_id
From (
SELECT productconfig_id, count(1) as _count
FROM association_productconfig_elementvariant
GROUP BY productconfig_id
HAVING variant_id IN (1, 2, 3, 99)
) as s where _count = 4;

Basicly the only productconfig with count = 4 will be the one you are looking for.