selecting rows that have n foreign key conditions satisfied I'm trying to filter a master table rows based on 2-more conditions from the relative foreign key relation, i came up with this which is working fine, but i just can't convince my self that it's that much of a fuss

select r.pageid
from
((
select co.pageid
from (cmscontentpublished as co join cmsnode as cm on co.PageID = cm.ID)
where cm.parentid=365 and co.Title='Title' and co.Content like '%test%'
)
union all
(
select co.pageid 
from (cmscontentpublished as co join cmsnode as cm on co.PageID = cm.ID)
where cm.parentid=365 and year(co.DateContent) =2012
)) as r
group by r.pageid
having COUNT(*)=2


any ideas on how to do correctly
Try if that works for you 

SELECT page_id FROM
(
SELECT co.pageid , count(case when year(co.DateContent) =2012 THEN 1 END) AS cnt1,
count(CASE WHEN co.Title='Title' and co.Content like '%test%' THEN 1 END) AS cnt2
FROM cmscontentpublished as co 
JOIN cmsnode as cm on co.PageID = cm.ID
WHERE cm.parentid=365 AND ((co.Title='Title' and co.Content like '%test%') OR
(year(co.DateContent) =2012))
)a 
WHERE (cnt1 + cnt2 )= 2 
-- if you want page_id only if each condition is satisfied exactly once,
-- then where should look "cnt1 = 1 and cnt2 =1"