MS SQL - Problem selecting a subset of records

I'm having a SQL brainfart moment. I am trying to get a set of records when any of the attribute IDs for that product is a certain value.

Problem is, I need to get all other attributes for that same product along with it.

Here's an illustration for what I mean:

Is there a way to do that? Currently I am doing this

select product_id
from mytable
where product_attribute_id = 154

But I obviously only get the single record:

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

EDIT

There's one condition I forgot to mention. There are times where I need to be able to filter on two attribute IDs. For example, in the first image above, the lower set (product ID 31039) has attribute id 395. I would need to filter on 15开发者_StackOverflow社区4, 395. The result would not include the top set (31046) which does not have an attribute id 395.

I think is what you're looking for:

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

In English: Get me all the records such that their product id is in the set of all product ids such that their attribute id is equal to @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)

That should do it.

Using proper joins, you can link back to the same table

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: to get products that have 2 attributes, you can join another time

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