$query ="SELECT DISTINCT product_id FROM fieldvals WHERE (fielddef_id IN (8,9) AND value LIKE '%$post%') ";

if ($bedrooms!= '0') {
$query .="AND (fielddef_id IN (12) AND value >= '$bedrooms') ";
}

if ($price!= '0') {
$query .="AND (fielddef_id IN (12) AND value >= '$price')";
}

-- *** Test Data ***
DECLARE @FieldVals TABLE
(
	product_id int NOT NULL
	,fielddef_id int NOT NULL
	,[value] varchar(50) NOT NULL
)
INSERT INTO @FieldVals
SELECT 1, 8, 'Some post junk'
UNION ALL SELECT 1, 9, 'Another post junk'
UNION ALL SELECT 1, 12, '12 bedrooms and 6 bathrooms'
UNION ALL SELECT 1, 24, 'The price is exorbitant'
UNION ALL SELECT 2, 8, 'Some post junk'
UNION ALL SELECT 2, 12, '12 bedrooms and 6 bathrooms'
UNION ALL SELECT 2, 24, 'The price is exorbitant'
UNION ALL SELECT 3, 12, '12 bedrooms and 6 bathrooms'
UNION ALL SELECT 4, 12, '12 bedrooms and 6 bathrooms'
UNION ALL SELECT 4, 24, 'The price is exorbitant'
-- *** End Test Data ***

SELECT product_id
FROM @FieldVals
WHERE (fielddef_id IN (8,9) AND [value] LIKE '%post%')
	OR (fielddef_id = 12 AND [value] LIKE '%bedrooms%')
	OR (fielddef_id = 24 AND [value] LIKE '%price%')
GROUP BY product_id
HAVING COUNT(*) >= 3

select distinct 
f1.product_id 
FROM fieldvals f1
WHERE (fielddef_id IN (8,9) AND value LIKE '%$post%')
and exists(select * from fieldvals where fielddef_id IN (12) AND value >= '$bedrooms' and product_id=f1.product_id)
and exists(select * from fieldvals where fielddef_id IN (24) AND value >= '$price' and product_id=f1.product_id)