SQL searching product based on multiple criteria (if attribute='length' then value70, if attribute='weight' then value3)

Question

I'm making a database project for college. The subject is construction shop (hammer, saws, screwdrivers etc.). The problem with this is that I can't really put everything in one table "product" because they have different attributes. So I asked my teacher how to set it up, and what he recommended is this.

I'm using Sybase Central with SQL Anywhere 12. It is what we are using, and was provided by college

Table for products called product

| Product_ID | Category | Manufacturer | Price | Amount in stock |

Table for attributes called attribute

| Attribute_ID | Attribute name |

And intersection of both of them called value

Product ID | Attribute ID | Value | Unit |

For example: In products table I have 3 hammers

| Product_ID | Category | Manufacturer | Price | Amount in stock |

| 1          | hammer   | man1         | 20    | 17
| 2          | hammer   | man2         | 14    | 20
| 3          | hammer   | man3         | 7    | 30

In attribute table:

| Attribute_ID | Attribute name |
| 1            | length         |
| 2            | weight         |

In value

| Product ID | Attribute ID | Value | Unit |
| 1          | 1            | 90    | cm   |
| 1          | 2            | 1     | kg   |
| 2          | 1            | 100   | cm   |
| 2          | 2            | 4     | kg   |
| 3          | 1            | 50    | cm   |
| 3          | 2            | 3     | kg   |

And then I use joins to get information, like this

SELECT product.product_id,
       product.category,
       product.manufacturer,
       product.price,
       product.amount_in_stock,
       attribute.name,
       value.value,
       value.unit
FROM product
INNER JOIN value ON product.product_id=value.value_id
INNER JOIN attribute ON attribute.attribute_id=value.attribute_id

Searching by one criteria is simple, I can just add one where. So if I want hammers longer than 80 cm I just add

WHERE product.category='hammer' AND attribute.name ='length' AND value.value>80

This would show me hammers 1 and 2.

So far, so good, but the problem beging when I try to search by multiple criteria. So for example hammer that are longer than 80 and weight more than 2. In example above, this would be only hammer 2. Adding OR or UNION doesn't work because then it will show me list of hammer that are longer that 80 cm and list of hammer that weight more than 2 kg, not that are both at the same time. If I add OR like this

WHERE (product.category='hammer' AND attribute.name ='length' AND value.value>80)
OR ((product.category='hammer' AND attribute.name ='weight' AND value.value>2)

It will show hammers 2 and 3, instead of just hammer 2.

I've tried doing some weird CASE like this

WHERE
CASE
   WHEN (CASE WHEN (attribute.name='length' THEN 1 ELSE 0)) IS NOT NULL THEN value.value>80)
   WHEN (CASE WHEN (attribute.name='weight' THEN 1 ELSE 0)) IS NOT NULL THEN value.value>2)
END

So that the nested CASE would return 1 if attribute is length and then it would check if value>80 and if attribute is not lenght then in would return 0 and do nothing, but it doesn't work.

I've also tried doing nested SELECT, but it seems that INNER JOIN doesn't work in nested select or I don't know how to write it.

SELECT product.product_id
FROM (
       SELECT product.product_id,
             product.category,
             product.manufacturer,
             product.price,
             product.amount_in_stock,
             attribute.name,
             value.value,
             value.unit
      FROM product
      INNER JOIN value ON product.product_id=value.value_id
      INNER JOIN attribute ON attribute.attribute_id=value.attribute_id
      WHERE product.category='hammer' AND attribute.name ='length' AND value.value>80
)
WHERE attribute.name='weight' and value.value>2

Is there anyway to write something like this

IF (attribute.name='length' THEN value>80)
IF (attribute.name='weight' THEN value>2)

Also I can't ask my teacher for this, because he's from database theory, not SQL, and the SQL teacher that I have to hand project to is not nice, and wouldn't help. There may also be small typos in columns name or code, because I had to translate names to english. So if there are any, they are not the problem

TL;DR

SELECT statment that will show hammer that are both longer than 80 cm and weight more than 2 kg. So, in the example above that would only be hammer 2. Hammer 1 is longer than 80 cm, hammer 3 weight more than 2 kg, but only hammer 2 is both at the same time.

user_1144035 · Answer

I would suggest aggregation. You haven't specified the database, but let met give you an example just returning the product_id:

SELECT p.product_id
FROM product p JOIN
     value v
     ON p.product_id = v.value_id JOIN
     attribute a
     ON a.attribute_id = v.attribute_id
WHERE p.category = 'hammer'   -- filter before the aggregation on this
GROUP BY p.product_id
HAVING SUM(CASE WHEN a.name = 'length' and v.value > 80 THEN 1 ELSE 0 END) A> 0 ND
       SUM(CASE WHEN a.name = 'weight' and v.value > 2 THEN 1 ELSE 0 END) > 0;

You can bring in additional attributes that you need for the final result.

Note that this assumes that value is a number. If not, you might get type conversion errors or erroneous string comparisons.

SQL searching product based on multiple criteria (if attribute='length' then value>70, if attribute='weight' then value>3)

1 Answers