Mysql JOIN multiple tables and select mulitple values

Question

First of all I want to apologize for my bad English. I have a problem with multiple tables. I'm not exactly a newby in the mySQL world but I cannot figure out a solution for this problem. For this problem I use 4 tables.

1. Categories
2. Products
3. Specifications
4. Specificationvalues

Every category has specifications and products and every specificationvalues has products and specification id's. Now the user can make a selection with different values. This is where my problem is. When the user selects value "green" and legs "4" I want all green products with 4 legs. So I used a JOIN (all kinds i think) to select the right product (example below)

SELECT DISTINCT products.id 
FROM products 
  LEFT JOIN specificationvalues ON specificationvalues.products_id = products.id 
  LEFT JOIN specifications ON specificationvalues.specifications_id = specifications.id 
WHERE specifications.name='materiaal' 
  AND specifications.name='kleur' 
  AND specificationvalues.id='77' 
  AND specificationvalues.id='78'

The problem is that all the values are in separate rows. That's why the WHERE doesn't work. I do not get MySQL error's. Only that it returns 0 rows.

I hope someone can help me! I got a lot of good things from this forum so I hope it will help me again!

---

I don't know why my changes yesterday where not saved. But here is my data again:

SPECIFICATIONS Table
ID   CATEGORIES_ID     NAME
38   297               Material
39   297               Measures
40   297               Color

SPECIFICATIONVALUES Table
ID   SPECIFICATIONS_ID  PRODUCTS_ID   VALUE
1    38                 988979        Masive wood 
2    39                 988979        24x57x98
3    40                 988979        Yellow
4    40                 988980        Black
5    39                 388980        24x57x98


PRODUCTS Table
ID         NAME
988979     Table
988980     Chair

So now I want all black prducts with measure 24x57x98. I hope you can help me!

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)

Comments

[raice]

YOUR A GENIUS! Thank you for your help DRapp! This was the solution

[DRapp]

@user1456684, as a newbie, when you have a question and get a solution, it's common courtesy to the one who offered the solution to up-check it and hit the checkmark next to their answer. This informs others that your question IS solved, and what answer worked. This helps others in the future who may have similar problems and what DID work for you that may work for them too.

[raice]

I'm sorry but there is still one problem. When I select 2 values, I still get the products that match 1 of the 2 values. Even when I used the AND.

[DRapp]

@user1456684, can you do a sample dump of the data records in question... samples from BOTH tables... only need the columns associated with the query, not all columns from the table.

[raice]

again thank you for your help! Here are the tables: table: Products id categories_id brands_id name description table: specifications id categories_id name order table: specificationvalues id specifications_id products_id value WAUW this form doesn't insert returns!

[DRapp]

@user1456684... NO... no... please see revised answer... show the DATA per my sample... we (the audience to your question) are missing something about the data and what records you EXPECT to be returned.

[raice]

I'm sorry, I replaced your example with my data.

[raice]

I don't know why my changes yesterday where not saved. But here is my data again: SPECIFICATIONS Table ID CATEGORIES_ID NAME 38 297 Material 39 297 Measures 40 297 Color SPECIFICATIONVALUES Table ID SPECIFICATIONS_ID PRODUCTS_ID VALUE 1 38 988979 Masive wood 2 39 988979 24x57x98 3 40 988979 Yellow 4 40 988980 Black 5 39 388980 24x57x98 PRODUCTS Table ID NAME 988979 Table 988980 Chair

[DRapp]

@raice, see revised answer for options to help you.