SQL JOIN multiple tables and subqueries

Question

I'm trying to write a query which will put values of size and colour (if available) of the product into the columns or 'null' where the is no parameters defined.

[Here is my BD]

all 'sizes' and 'colours' are in 'options_values_translations' table and are linked via ovalue_id. From there I need sizes and I can get them by this query:

SELECT
      options_values_translations.ovalue_id,
      options_values_translations.value
    FROM options_values_translations
    WHERE options_values_translations.value LIKE '%cm'

and colours by this:

SELECT
      options_values_translations.ovalue_id,
      options_values_translations.value
    FROM options_values_translations
    WHERE options_values_translations.value IN ('blue', 'pink')

I used query designer and result is the following:

[Query designer view]

SELECT
  products.product_id AS ID,
  SUBSTRING_INDEX (products_stock.code, "_", -1) AS tw_symbol,
  size.value AS Size,
  colour.value AS Colour
FROM products
  INNER JOIN products_stock
    ON products_stock.product_id = products.product_id
  INNER JOIN options_stock_values
    ON options_stock_values.stock_id = products_stock.stock_id
  LEFT OUTER JOIN (SELECT
      options_values_translations.ovalue_id,
      options_values_translations.value
    FROM options_values_translations
    WHERE options_values_translations.value LIKE '%cm') size
    ON options_stock_values.ovalue_id = size.ovalue_id
  LEFT OUTER JOIN (SELECT
      options_values_translations.ovalue_id,
      options_values_translations.value
    FROM options_values_translations
    WHERE options_values_translations.value IN ('blue', 'pink')) colour
    ON options_stock_values.ovalue_id = colour.ovalue_id
WHERE products_stock.active = 1
AND products_stock.code REGEXP '^[0-9]*_[0-9a-zA-Z/ ]*$'
AND products.product_id IN (244, 2107)
ORDER BY ID

Note: Products with IDs 244, 2107 are just a test products :) so pls do not focus on WHERE clause.

Result is the following:

ID       tw_symbol    Size          Colour    
-------- ------------ ------------- ----------
244      X3           13x18 cm      NULL      
244      X0           NULL          blue
244      X1           NULL          pink    
244      X2           NULL          blue      
244      X3           NULL          pink    
244      X0           9x13 cm       NULL      
244      X1           9x13 cm       NULL      
244      X2           13x18 cm      NULL      
2107     VR 17029     8.5 cm     NULL      
2107     VR 17028     6.5 cm     NULL  

My expected result is the following:

ID       tw_symbol    Size          Colour    
-------- ------------ ------------- ----------
244      X3           13x18 cm      pink      
244      X0           9x13 cm       blue      
244      X1           9x13 cm       pink    
244      X2           13x18 cm      blue      
2107     VR 17029     8.5 cm     NULL      
2107     VR 17028     6.5 cm     NULL      

Simply speaking ... I need one row only per each tw_symbol.

Product with ID 244 does have both colours AND sizes defined.
Product with ID 2107 does have sizes but NOT colours.
There are also products without colour and size where i would like to have 2x null in columns size and colour.

The following query shows product 244 correctly but does not show 2107 at all:

SELECT
  products.product_id AS ID,
  SUBSTRING_INDEX (products_stock.code, "_", -1) AS tw_symbol,
  size.value AS Size,
  colour.value AS Colour
FROM products
  INNER JOIN products_stock
    ON products_stock.product_id = products.product_id
  INNER JOIN options_stock_values
    ON options_stock_values.stock_id = products_stock.stock_id
  INNER JOIN (SELECT
      options_values_translations.ovalue_id,
      options_values_translations.value
    FROM options_values_translations
    WHERE options_values_translations.value LIKE '%cm') size
    ON options_stock_values.ovalue_id = size.ovalue_id
  INNER JOIN options_stock_values options_stock_values_1
    ON options_stock_values_1.stock_id = products_stock.stock_id
  INNER JOIN (SELECT
      options_values_translations.ovalue_id,
      options_values_translations.value
    FROM options_values_translations
    WHERE options_values_translations.value IN ('blue', 'pink')) colour
    ON options_stock_values_1.ovalue_id = colour.ovalue_id
WHERE products_stock.active = 1
AND products_stock.code REGEXP '^[0-9]*_[0-9a-zA-Z/ ]*$'
AND products.product_id IN (244, 2107)
ORDER BY ID

Could anyone please correct my main query to provide me an expected result ? I got completely lost in JOINs :(

question from:https://stackoverflow.com/questions/65545489/sql-join-multiple-tables-and-subqueries

Answer 1

You need group by as follows:

SELECT
  products.product_id AS ID,
  SUBSTRING_INDEX (products_stock.code, "_", -1) AS tw_symbol,
  Max(size.value) AS Size,
  Max(colour.value) AS Colour
FROM products
  INNER JOIN products_stock
    ON products_stock.product_id = products.product_id
  INNER JOIN options_stock_values
    ON options_stock_values.stock_id = products_stock.stock_id
  LEFT OUTER JOIN (SELECT
      options_values_translations.ovalue_id,
      options_values_translations.value
    FROM options_values_translations
    WHERE options_values_translations.value LIKE '%cm') size
    ON options_stock_values.ovalue_id = size.ovalue_id
  LEFT OUTER JOIN (SELECT
      options_values_translations.ovalue_id,
      options_values_translations.value
    FROM options_values_translations
    WHERE options_values_translations.value IN ('blue', 'pink')) colour
    ON options_stock_values.ovalue_id = colour.ovalue_id
WHERE products_stock.active = 1
AND products_stock.code REGEXP '^[0-9]*_[0-9a-zA-Z/ ]*$'
AND products.product_id IN (244, 2107)
Group by products.product_id,
         SUBSTRING_INDEX (products_stock.code, "_", -1)
ORDER BY ID