Mysql JOIN multiple tables and select mulitple values

join, mysql, rows

Solution

I've seen many instances of tables that have rows to simulate representing "columns" where the name is a descriptor and the "id" or "value" is the associated want value.

What you need to consider is what does ONE ROW look like. Your join from specifications name to the specifications values. Does the '77' value only correspond to the 'material' spec, 'kleur', or both... likewise with 78. You could have a variety of combinations, such as

where ( specifications.name = 'material' and specificationValues.id = '77' )
  OR  ( specifications.name = 'kleur' and specificationValues.id = '78' )

Or, if the specification values ID is regardless of the spec name, you could use

where specifications.name in ('material', 'kleur' )
  AND speciificationValues.ID in ( '77', '78' )

PER YOUR REVISED SAMPLE DATA... In this type of criteria you want, I would do by applying a double-join to represent each criteria you want, such as:

select p.*,
       sv1.Value as NameOfColor,
       sv2.Value as ProductMeasurement
   from
      Products p

         JOIN SpecificationValues sv1
            on p.ID = sv1.products_id
           AND sv1.Value = 'Black'
            JOIN Specifications s1
               on sv1.Specifications_ID = s1.ID
              AND s1.Name = 'Color'

         JOIN SpecificationValues sv2
            on p.ID = sv2.products_id
           AND sv2.Value = '24x57x98'
            JOIN Specifications s2
               on sv2.Specifications_ID = s2.ID
              AND s2.Name = 'Measures

Now, may look complex, but look at the simplicity (by explicit spacing between join segments). However, if you ever wanted to add more "crieria" requirements, just duplicate by creating a similar sv3, sv4, sv5 set... Now, that said, if you are building this out dynamically where a user can pick more things, and you are providing some sort of "selection" of the Readable description (Color, Measure, Material), then just preserve the IDs so you don't need the extra join, just know the actual ID, and it would be simplified to...

select p.*,
       sv1.Value as NameOfColor,
       sv2.Value as ProductMeasurement
   from
      Products p

         JOIN SpecificationValues sv1
            on p.ID = sv1.products_id
           AND sv1.Specifications_ID = 40
           AND sv1.Value = 'Black'

         JOIN SpecificationValues sv2
            on p.ID = sv2.products_id
           AND sv2.SpecificationsID = 39
           AND sv2.Value = '24x57x98'

Now, back to original answer, you could get the same results (provided you never have a color of "24x57x98", or a measurement of "Black" per your example data. You can apply the IN (list of codes) AND IN (list of values) and using a HAVING clause to ensure proper count of matched elements IS found. My FINAL query would not use "Products" as the first table, but the second because you could have 10,000 products, but only 142 that are of a given size... start by the table/criteria of a smaller set and join to the products to get the name.

select
      p.id,
      p.name
   from
      specificationValues sv
         join products p
            on sv.products_id = p.id
   where
          sv.specifications_ID IN ( 39, 40 )
      AND sv.value in ( 'Black', '24x57x98' )
   group by
      p.id
   having
      count(*) = 2

Ensure your specificationValues table has an index on (specifications_ID, value). This way, the index can match HOW you are looking for data. Some may even suggest having all 3 parts in the index for (specifications_ID, value, products_id)

Problem

Original source