SQL select statement dynamical multiple conditon

Question

I have 3 tables, say images(id), news(id), types(id, category, obj_id, type)

For example, categories of news A are black, red.

data struct is like

{
  types(xxxx1,red,news_A,news)
  types(xxxx2,black,news_A,news)
}

now I need find out all images with type red and black.

In this case, I need images_B

{
  types(oooo1,red,images_B,images)
  types(oooo2,black,images_B,images)
  types(oooo3,red,images_C,images)
  types(oooo4,red,images_D,images)
  types(oooo5,black,images_E,images)
}

Obviously, I can't write

select obj_id from types 
where category in (select category from types where obj_id = news_A) 
and type = images.

Because, like that, it will return images_B,C,D,E. I only need images_B.

Category is also dynamical. It could be red,blue,pink......

Answer 1

One approach

SELECT * INTO #types
FROM 
(SELECT 'xxxx1' AS id,'red'  AS category,'news_A' AS obj_id,'news' AS [type] UNION ALL
SELECT 'xxxx2' AS id,'black'  AS category,'news_A' AS obj_id,'news' AS [type] UNION ALL
SELECT 'oooo1' AS id,'red'  AS category,'images_B' AS obj_id,'images' AS [type] UNION ALL
SELECT 'oooo2' AS id,'black'  AS category,'images_B' AS obj_id,'images' AS [type] UNION ALL
SELECT 'oooo3' AS id,'red'  AS category,'images_C' AS obj_id,'images' AS [type] UNION ALL
SELECT 'oooo4' AS id,'red'  AS category,'images_D' AS obj_id,'images' AS [type] UNION ALL
SELECT 'oooo5' AS id,'black'  AS category,'images_E' AS obj_id,'images' AS [type]) X

declare @target varchar(10)
set @target = 'news_A'

;with  ConcatTypes As
(
SELECT obj_id,
  (
        select distinct '' + t.[category] as cat
        from #types t
        where t.obj_id = t1.obj_id
        order by cat
        for xml path('')
    ) as catlist
FROM #types t1
GROUP BY obj_id
)
SELECT obj_id, catlist 
FROM ConcatTypes
WHERE obj_id<> @target and catlist = 
          (select catlist FROM ConcatTypes where obj_id=@target)