Hi all,

I have a table that has an id (and other fields) :
Table1_id
....

Then I have another  table with also an id (and other fields) :
Table2_id
....

And I have my pivot table for the 'many to many' relationship :
pivot_idTable1
pivot_idTable2

Let's say the pivot table contains these rows :
1, 1
2, 1
2, 2
3, 1
3, 2
3, 3

What I want is a stored proc to list all the Table1 entries that has
EVERY Table2 ids that I provide. For example, if I'd execute my stored
proc as follow :
EXEC sp_GetTable1Rows('1, 2')
That should return me the rows 2 and 3 of the Table1, because only
those twos contains both the Table2 ids 1 and 2.
If I execute :
EXEC sp_GetTable1Rows('1, 2, 3')
That should return me only the row 3 of the Table1, because only that
row contains the Table2 ids 1, 2 and 3.

Are my explanations clear enough? :S
Thanks for all your help!

Here is a solution in SQL Server 2005 with the sample data...

create table pivottbl (pivot_idTable1 int,pivot_idTable2 int)

insert into pivottbl values(1, 1)
insert into pivottbl values(2, 1)
insert into pivottbl values(2, 2)
insert into pivottbl values(3, 1)
insert into pivottbl values(3, 2)
insert into pivottbl values(3, 3)

create table mytbl1 (Table1_id int)
insert into mytbl1 values(1)
insert into mytbl1 values(2)
insert into mytbl1 values(3)

create proc sp_GetTable1Rows
@a varchar(100)
as
begin
with tbl_for_csv as
(
select left(@a + ',,',charindex(',',@a + ',,') -1)as Col,
right(@a + ',,',len(@a + ',,') - charindex(',',@a + ',,')) as Str
union all
select left(Str,charindex(',',Str) - 1)as Col,
right(Str,len(Str) - charindex(',',Str)) from tbl_for_csv
where len(right(Str,len(Str) - charindex(',',Str))) > 0
)
select * from mytbl1
where Table1_id in
(Select pivot_idTable1 from pivottbl
   where pivot_idTable2 in (select col from tbl_for_csv)
group by pivot_idTable1
having count(*) = (Select count(*) from tbl_for_csv)
)
end

EXEC sp_GetTable1Rows '1'
EXEC sp_GetTable1Rows '1, 2'
EXEC sp_GetTable1Rows '1, 2, 3'


In SQL Server 2000, instead of the Common table expression that I used to
split the csv input you can use the technique described here and the rest of
the proc should be the same..

www.sommarskog.se/arrays-in-sql.html

Hope this helps...

Omnibuzz,

I borrowed your DDL and DML, thanks!

create table pivottbl (pivot_idTable1 int,pivot_idTable2 int)
SET NOCOUNT ON

insert into pivottbl values(1, 1)
insert into pivottbl values(2, 1)
insert into pivottbl values(2, 2)
insert into pivottbl values(3, 1)
insert into pivottbl values(3, 2)
insert into pivottbl values(3, 3)

go
CREATE PROCEDURE SelectMatches
  @Table2IDs VARCHAR(10)
AS
PRINT @Table2IDs
SELECT pivot_idTable1 FROM pivottbl
where ',' + @Table2IDs + ',' LIKE '%,' + CAST(pivot_idTable2 AS
VARCHAR(5)) + ',%'
GROUP BY pivot_idTable1
HAVING COUNT(*) = (LEN(@Table2IDs) - LEN(REPLACE(@Table2IDs, ',', ''))
+ 1)
go
EXEC dbo.SelectMatches '1'
EXEC dbo.SelectMatches '1,2'
EXEC dbo.SelectMatches '1,3'
EXEC dbo.SelectMatches '1,2,3'
EXEC dbo.SelectMatches '1,2'
go
drop table pivottbl
go
DROP PROCEDURE SelectMatches

Also one could use a UDF and parse a comma separated list into a
results set and join with it, as described in Erland Sommarskog's
article on Arrays and Lists in SQL.

Primary Key
Postcode problem
Query Problem
Creating 2nd transaction form inside db ?
About Cursor on Dynamically created Tables.
Mitery variable
DATEPART Gives different answers on different machines?!
Query working in EM and QA but not in Store proc
SQL Emergency! Need Help
String concatenations