Multiple joins with condition?

Question

MySQL

SELECT 
  t1.id,
  t1.name AS product,
  t3.name AS color,
  t5.name AS size
FROM
  `product` AS t1
JOIN
  `product_color` AS t2 ON t1.id = t2.product_id
JOIN
  `color` AS t3 ON t2.color_id = t3.id
JOIN 
  `product_size` AS t4 ON t1.id = t4.product_id
JOIN 
  `size` AS t5 ON t4.size_id = t5.id

Question:
How to get a list of products that satisfies all the conditions listed below at once?

• black color and size S
  
• black color and size M
  
• size L
  

Pseudocode:

WHERE
 (color = "black" and size = "S")
and
 (color = "black" and size = "M")
and
 (size = "L")

Please note that the "color" and "size" properties are fields of different tables.
There are only two products in the table and "jacket A" satisfies all the listed conditions.
In reality, there are many highly specialized entities, the properties of these entities are stored using N:M. Some of these properties allow nesting, as a result of which, a normal select turns into a string of solid length, not to mention a query with a condition. Maybe I missed something, poke your finger if it's not difficult.

Answer 1

In my opinion, everything is possible :-) because
I would do it through subquery (convenient for sampling according to different conditions from a compiled "set" of intersections)

select BSproduct from
  ( -- Список продуктов имеющих черный цвет и малый размер
    select product as BSProduct from
      (
        SELECT  t1.id,  t1.name AS product,  t3.name AS color,  t5.name AS size
          FROM  `product` AS t1
          JOIN `product_color` AS t2 ON t1.id = t2.product_id
          JOIN `color` AS t3 ON t2.color_id = t3.id
          JOIN `product_size` AS t4 ON t1.id = t4.product_id
          JOIN `size` AS t5 ON t4.size_id = t5.id
      ) 
    where color = "black" and size = "S"  
  ) as BlackAndSmallSize, 
  ( -- Список продуктов имеющих черный цвет и средний размер
    select product as BMProduct from
      (
        SELECT  t1.id,  t1.name AS product,  t3.name AS color,  t5.name AS size
          FROM  `product` AS t1
          JOIN `product_color` AS t2 ON t1.id = t2.product_id
          JOIN `color` AS t3 ON t2.color_id = t3.id
          JOIN `product_size` AS t4 ON t1.id = t4.product_id
          JOIN `size` AS t5 ON t4.size_id = t5.id
      ) 
    where color = "black" and size = "M"  
  ) as BlackAndMidSize, 
  ( -- Список продуктов имеющих большой размер
    select product as LProduct from
      (
        SELECT  t1.id,  t1.name AS product,  t3.name AS color,  t5.name AS size
          FROM  `product` AS t1
          JOIN `product_color` AS t2 ON t1.id = t2.product_id
          JOIN `color` AS t3 ON t2.color_id = t3.id
          JOIN `product_size` AS t4 ON t1.id = t4.product_id
          JOIN `size` AS t5 ON t4.size_id = t5.id
      ) 
    where size = "L"  
  ) as LargeSize
where BSProduct=BMProduct and BMProduct=LProduct

I don't know about MySQL and its forks, in some SQL servers you can pre-define subqueries like

(
        SELECT  t1.id,  t1.name AS product,  t3.name AS color,  t5.name AS size
          FROM  `product` AS t1
          JOIN `product_color` AS t2 ON t1.id = t2.product_id
          JOIN `color` AS t3 ON t2.color_id = t3.id
          JOIN `product_size` AS t4 ON t1.id = t4.product_id
          JOIN `size` AS t5 ON t4.size_id = t5.id
      )

using the WITH command - then the amount of text in the selection will be reduced significantly.

Answer 2

so write as you said:

WHERE
 (t3.name = 'black' and t5.name = 'S')
and
 (t3.name = 'black' and t5.name = 'M')
and
 (t5.name = 'L')

WHERE already filters the results of the join and all these fields will be there. Do not forget that aliases cannot be used in WHERE - it will be executed before SELECT, where these aliases are defined