SQL select statement dynamical multiple condition

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......

Martin Smith · Accepted Answer · 2010-08-16 21:09:06Z

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)

Thx Martin. But I cant hardcode [types] as you described. real types table is a thouands of lines table
You don't have to. That bit was just for demo purposes. Just skip that bit and start off with ;with ConcatTypes As and select from your types table there. I've changed it to refer to a temp table instead now.

Collectives™ on Stack Overflow

SQL select statement dynamical multiple condition

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related