MS SQL - the problem of selecting a subset of records

Question

I have a SQL brain moment. I am trying to get a recordset when any of the attribute identifiers for this product is a specific value.

The problem is that I need to get all the other attributes for the same product.

Here is an illustration of what I mean:

Is there any way to do this? I'm currently doing this

select product_id
from mytable
where product_attribute_id = 154

But I obviously get only one entry:

Any help would be greatly appreciated. My SQL skills are a bit basic.

EDIT

, . , . , ( 31039) 395. 154, 395. (31046), 395.

Answer 1

, , :

SELECT * myTable where Product_Id IN (SELECT Product_Id FROM MyTable WHERE Product_AttributeID = @parameterValue)

-: , , @parameterValue.

EDIT:

SELECT * myTable where Product_Id IN (SELECT Product_Id FROM MyTable WHERE Product_AttributeID = @parameterValue1) AND Product_Id IN (SELECT Product_Id FROM MyTable WHERE Product_AttributeID = @parameterValue2)

.

Answer 2

,

select B.*
from mytable A
-- retrieve B records from A record link
inner join mytable B on B.product_id = A.product_id
where A.product_attribute_id = 154  -- all the A records

EDIT: , 2 ,

select C.*
from mytable A
-- retrieve B records from A record link
inner join mytable B on B.product_id = A.product_id
inner join mytable C on C.product_id = A.product_id
where A.product_attribute_id = 154  -- has attrib 1
  AND B.product_attribute_id = 313  -- has attrib 2