Help with MS SQL Query

[kontakt07]

Hello,

I need some help with a sql query. Here's my scenario:

I have a table like this:

ID | Value

10 | 45
10 | 46
10 | 47
11 | 45
11 | 46
12 | 45

What I need to do is create a stored procedure that would return mutual exclusive set of values for any number of id's passed in as parameters.

For instance:

Parameters passed in: id = 10, id = 11

Return set:

45
46

(47 would not be in the set, since it's not common to both 10 and 11).

Example 2.

Parameters passed in: id = 10, id = 11, id = 12

Return set:

45

(that's the only number all three IDs have in common).

I do not know the number of IDs that should be passed in since that depends on user selected inputs.

Any help would be GREATLY appreciated.

Thank You

[kontakt07]

Any help on this? Anybody?

Thanks

[ck9663]

Hello,

I need some help with a sql query. Here's my scenario:

I have a table like this:

ID | Value

10 | 45
10 | 46
10 | 47
11 | 45
11 | 46
12 | 45

What I need to do is create a stored procedure that would return mutual exclusive set of values for any number of id's passed in as parameters.

For instance:

Parameters passed in: id = 10, id = 11

Return set:

45
46

(47 would not be in the set, since it's not common to both 10 and 11).

Example 2.

Parameters passed in: id = 10, id = 11, id = 12

Return set:

45

(that's the only number all three IDs have in common).

I do not know the number of IDs that should be passed in since that depends on user selected inputs.

Any help would be GREATLY appreciated.

Thank You

try something like this:

select value from YourTable
where ID in (@para1, @para2...and so on)
group by value having count(*) > 1

[azimmer]

A complete one:

create procedure foundinall(@idcsv as varchar(200))
as
declare @pos as int, @lastpos as int
declare @temptbl TABLE (tmpid int)
 
set @lastpos = 1
set @pos = charindex(',',@idcsv)
while (@pos > 0)
begin
	insert into @temptbl values (substring(@idcsv,@lastpos,@pos-@lastpos))
	set @lastpos=@pos+1
	set @pos=charindex(',',@idcsv,@lastpos)
end
insert into @temptbl values (substring(@idcsv,@lastpos,len(@idcsv)-@lastpos+1))

select val from idvalue
where ID in (select tmpid from @temptbl)
group by val having count(*) = (select count(*) from @temptbl)

go

Usage: exec foundinall '10,11,12'

[kontakt07]

Thank You so much AZIMMER!

Your code worked without a glitch on the first try. Exactly what I needed and your code does it perfectly. Many thanks.