SQL - how to display products' options, whether or not they have them?

Question

Assume a products can have 0 or more options -- implemented with the following tables:

products
 - id
 - name

options
 - id
 - name

product_options
 - id
 - product_id
 - option_id

Further assume the following products have the following options:

• Product 1 = Option 1, Option 2, Option 3
• Product 2 = Option 2, Option 3, Option 4
• Product 3 = Option 3, Option 4, Option 5

How can I query this so that I get results like this:

• Product 1, Option 1, Option 2, Option 3, NULL, NULL
• Product 2, NULL, Option 2, Option 3, Option 4, NULL
• Product 3, NULL, NULL, Option 3, Option 4, Option 5

My options are actually a nested tree. And they have a foreign key to a categories table (also a nested tree). Ultimately, I need to be able to do this query and group the results by category. However, I probably should understand first how to solve this simpler version of my problem.

UPDATE 1: I do not know in advance what the options might be. Also, there is no limit to the number of options a product may have.

Answer 1

If you have an unknown number of options you could use a stored procedure to dynamically create a query that can be used to pivot your table. Something like this:

CREATE PROCEDURE display_options()
BEGIN
  SET @query = 'SELECT p.id, ';
  SET @query = CONCAT(@query, (SELECT GROUP_CONCAT(CONCAT('MAX(CASE WHEN o.name = ''', name, ''' THEN o.name END) AS `', name, '`')) FROM options ORDER BY id));
  SET @query = CONCAT_WS(' ', @query, 
                         'FROM products p',
                         'JOIN product_options po ON po.product_id = p.id',
                         'JOIN options o ON o.id = po.option_id',
                         'GROUP BY p.id');
  PREPARE stmt FROM @query;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END

This procedure will produce a query like this (essentially the same as in @GordonLinoff's answer for the sample data in your question):

SELECT p.name, 
       MAX(CASE WHEN o.name = 'Option 1' THEN o.name END) AS `Option 1`,
       MAX(CASE WHEN o.name = 'Option 2' THEN o.name END) AS `Option 2`,
       MAX(CASE WHEN o.name = 'Option 3' THEN o.name END) AS `Option 3`,
       MAX(CASE WHEN o.name = 'Option 4' THEN o.name END) AS `Option 4`,
       MAX(CASE WHEN o.name = 'Option 5' THEN o.name END) AS `Option 5` 
FROM products p 
JOIN product_options po ON po.product_id = p.id 
JOIN options o ON o.id = po.option_id 
GROUP BY p.name

which can then be prepared and executed to give results like this:

name        Option 1    Option 2    Option 3    Option 4    Option 5
Product 1   Option 1    Option 2    Option 3        
Product 2               Option 2    Option 3    Option 4    
Product 3                           Option 3    Option 4    Option 5

demo on dbfiddle