Hoping for some help writing a query ...
I have a Products Table and it has a Child table "Colors." Let's say the Product is a Box and the Colors are Red, Green and Blue. I have an Criteria table "MatchColors" which may contain all or a subset of those colors.
I need to fetch a list of products that have *every* color matched. That is, take a Product and for each Color record there is a matching MatchColor Record. I need to return a list of products where ALL their colors are found the in MatchColor table. I am having trouble because I am returning records that match at least one product, but I have to ensure that every color has a matching record.
Colors --- MatchColors
colorID (pkey) matchColorID (pkey)
colorName colorID (column to match up)