Mysql data fetch problem I am facing help me here php and mysql guru

I have a table structure :

Product Table

catid Pid pname
-------------------
1 1 Shirt
1 2 Pant

Product Attribut table

Pid attributename attributevale
------------------------------------
1 color red
1 Material silk
2 color red
2 size 34


Now I want a query to fetch the "pid" where color=red and size=34

It should display only the product 2 That is pant only. Not to select product shirt and pant.

Can anybody here to help me with this sql




Thanks in advance,
Subikar

 

I guess something like this:

SELECT pname FROM `product` INNER JOIN `attribut` ON attibut.pid=product.pid WHERE attribut.name="color" AND attribut.value="red" AND attribut.pid IN (SELECT pid FROM `attribut` WHERE attribut.name="size" AND attribut.value="34")

Code (markup):

I didn't test it