filter by multiple attributes

Question

I have the table :

id id_products id_atribut   name     value
1     13          8        autdio      2.1
2     13          9        hdd         200 Gb
3     13          10       cd-rom       2
4     20          8         audio       2.1

the problem is, how can i select from this table where name="audio" and value="2.1" and name="hdd" and value="200 gb" and return id_products=13 ....

How can i do this?

Answer 1

Em... like this? Or what do you want?

SELECT * FROM table
WHERE
id_products=13 and name="audio" and value="2.1" and name="hdd" and value="200 gb"

Answer 2

My guess is that what you want is actually to select all the rows, by using

SELECT * FROM table

however you could also be referring to having variables in your statement for testing purposes

example:

SET @var_name = expr [, @var_name = expr] ...

For SET, either = or := can be used as the assignment operator.

You can also assign a value to a user variable in statements other than SET. In thiscase, the assignment operator must be := and not = because = is treated as a comparison operator in non-SET statements:

mysql

then you use them as follows:

SET @t1='audio';

mysql> SELECT @t1 FROM table;

that way you will always get the same result for your search and you can use the variable as needed. I also noticed that your audio is not spelled the same autdio, can I assume that this is a typo. The biggest guess here is that you want to select either everything from the table to view it, like the first bit of code posted, or you want to select only key fields.

If you are selecting SELECT * FROM tablename WHERE id_products=13 AND name="audio" AND value="2.1" AND name="hdd" AND value="200 gb" the problem remains that you never get any row in the database that matches this criteria.

Answer 3

I think what you want is to query your table based on attribute values, so you want to search for the products that have attribute audio equals 2.1 and attribute hdd equals 200 Gb. In your example, it should return the product 13.

You can do that by joining with the same table multiple times with different aliases.

Here is an example assuming you have a table products containing your products and the table you shown us is called product_attributes

select id
from products
inner join product_attributes attribute_audio 
    on products.id = attribute_audio.product_id 
    and attribute_audio.id_atribut = 8
inner join product_attributes attribute_hdd 
    on products.id = attribute_audio.product_id 
    and attribute_hdd.id_atribut = 9
where attribute_audio.value = '2.1'
and attribute_hdd.value = '200 Gb'

If you want to dynamically change the attributes you want to use in your query, you will have to generate your query yourself before executing it.