Hi,

please I need a query that performs the following:

I have a table like this:

set_id | rec_id
10 | 1
10 | 25
10 | 32
20 | 61
20 | 90
30 | 77

Now I have suppose rec_id = 61 and rec_id = 90 I need to get the set_id that consist of both which is 20 .
set 20: (61,90).

Thank u in advance

Is the rec_id unique? If so there is another way to go about this. Here is one way:

IF OBJECT_ID('TestTable') IS NOT NULL DROP TABLE TestTable
Create Table TestTable
(
  set_id int,
  rec_id int
)
Insert Into TestTable (set_id, rec_id) Values(10, 1)
Insert Into TestTable (set_id, rec_id) Values(10, 25)
Insert Into TestTable (set_id, rec_id) Values(10, 32)
Insert Into TestTable (set_id, rec_id) Values(20, 61)
Insert Into TestTable (set_id, rec_id) Values(20, 90)
Insert Into TestTable (set_id, rec_id) Values(30, 77)

GO

Select DISTINCT(set_id) As set_id
From TestTable
Where rec_id In (1, 25) and
IsNull((
  Select COUNT(set_id)
  From TestTable x
  Where x.rec_id in (1, 25) and x.set_id = TestTable.set_id
), 0) = 2 --The "2" needs to match the # in your IN() stmt

Hi sknake,

This works pretty good :)

Thank you alot.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.