Check if an option matches all the value in a list-CodePudding

I have two tables in MySQL, products and product_options. A product can have more than an option type.

Table product:

id	name
1	Croissant
2	Croissant Medium
3	Coffee

Table product_options:

id	type	value	productId
1	weight	40	1
2	weight	60	2
3	weight	6	3
4	size	small	3

As you can see, all the products have the option type weight set, while the option type size is set only for the product with id 3.
I'm trying to have a query only, that returns if a product option is set for all the products.

I'm expecting as result a mysqli object with a boolean for each product option type, to flag if the product option type is set for all the products.

In the example case should be:

optionType	allProductsHaveIt
weight	true
size	false

CodePudding user response：

SELECT product.id,
       product.name,
       GROUP_CONCAT(product_options.type) present_options,
       GROUP_CONCAT(CASE WHEN product_options.type IS NULL
                         THEN options_list.type
                         END ) absent_options
FROM product
CROSS JOIN ( SELECT DISTINCT type
             FROM product_options ) options_list
LEFT JOIN product_options ON product.id = product_options.productId
                         AND options_list.type = product_options.type
GROUP BY product.id, product.name;

What I need, is given the list of options, what option is present in all the products.

SELECT product_list_per_option.type,
       products_list.products_list IS NOT NULL AllProducesHaveIt
FROM ( SELECT type, GROUP_CONCAT(productId ORDER BY productId) products_list
       FROM product_options
       GROUP BY type ) product_list_per_option
LEFT JOIN ( SELECT GROUP_CONCAT(id ORDER BY id) products_list
            FROM product ) products_list USING (products_list)

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=257439582a57eb8e9d81bbb8de2958aa