think I'm losin it ..

[chocoloco]

How do you query a table in Sql Server so that ....

You have a EmployeeProjectAssignment table where Employees are associated with Projects. One employee can have many projects. The task is ...

Create a Sproc, which will take a string of project Ids, and return all the employees that are associated to every one of those projects??? It should be fairly easy but I am not able to figure it out????

[mendhak]

It is not you who queries the table. It is the query that tables you.

You're given a bunch of IDs, I'm assuming you know how to split them up and loop them up for the query.

For any given ID, simply query EmployeeProjectAssignment, do a

SELECT EmployeeID FROM EmployeeProjectAssignment WHERE ProjectID = '123'

Of course, since you'll be doing it in a loop, you should store the results from each iteration of the query into a temporary table.

Any further refinement on the query or resultset is a matter of your end requirements.

[timeshifter]

The other idea would be just ask the table...

[chocoloco]

It is not you who queries the table. It is the query that tables you.

You're given a bunch of IDs, I'm assuming you know how to split them up and loop them up for the query.

For any given ID, simply query EmployeeProjectAssignment, do a

SELECT EmployeeID FROM EmployeeProjectAssignment WHERE ProjectID = '123'

Of course, since you'll be doing it in a loop, you should store the results from each iteration of the query into a temporary table.

Any further refinement on the query or resultset is a matter of your end requirements.

Thanks, but there can be as many as 5000 users and 500 projects, if I were to loop 5000 times and check every one of 500 projects (which is possible, not very likely but possible), its going to kill the performance completely. I was hoping there'd be a better and more logical approach to this.

[mendhak]

You don't have to loop.

Split the IDs up, place into a temporary table. Then perform a left join from that temporary table on the EmployeeProjectAssignment table.

[MasterBlaster]

It is not you who queries the table. It is the query that tables you.

You're given a bunch of IDs, I'm assuming you know how to split them up and loop them up for the query.

For any given ID, simply query EmployeeProjectAssignment, do a

SELECT EmployeeID FROM EmployeeProjectAssignment WHERE ProjectID = '123'

Of course, since you'll be doing it in a loop, you should store the results from each iteration of the query into a temporary table.

Any further refinement on the query or resultset is a matter of your end requirements.

or you could pass it in as a comma delimited varchar parameter and convert it to comma delimeted ints

SELECT EmployeeID FROM EmployeeProjectAssignment WHERE ProjectID in (123, 312,124,125)

If you need help writing a function to convert it from varchar's to ints let me know, I think I have one laying around somewhere.

Keep in mind the varchar is limited to 8000 so it won't scale past that.

[chocoloco]

But If I use IN clause, it will also return the employees who have access to project '123' only. What I need is the employees who have access to 123, 312, 124 and 125. (all of them).

Mendhak - I do not know how LEFT JOIN is going to return me above mentioned result. Can you write a query??? Just assume there is a table called projectEmployeeAssignment which have 3 columns. AssignmentId (PK), ProjectId, EmployeeId.

[mendhak]

Oh, looks like we misunderstood you. Or at least I did.

You want all employees associated to ProjectID 123 AND 312 AND 124 AND 125.

Hmmm.

[chocoloco]

Oh, looks like we misunderstood you. Or at least I did.

You want all employees associated to ProjectID 123 AND 312 AND 124 AND 125.

Hmmm.

That's right.

[mendhak]

OK, I have a crude idea.

DECLARE @Temp VARCHAR(20)
SELECT @Temp =  COALESCE( @Temp + ', ','') + CONVERT(VARCHAR(20), ProjId)  FROM EmpProjTemp WHERE EmpID = 1
PRINT @Temp

So what happens here is you get a string:

123, 456, 789

I created a UDF:

CREATE FUNCTION dbo.GetProjectIds (@EmpId INT)  
RETURNS VARCHAR(200) AS  
BEGIN 

DECLARE @Temp VARCHAR(20)
SELECT @Temp =  COALESCE( @Temp + ', ','') + CONVERT(VARCHAR(20), ProjId)  FROM EmpProjTemp WHERE EmpID = @EmpID
RETURN @Temp
END

Then used it in a statement:

SELECT DISTINCT EmpID FROM EmpProjTemp WHERE dbo.GetProjectIDs(EmpID) = '123, 456, 789, 666'

[mendhak]

So all you need to do is get the parameters as a string of comma separated values.

[chocoloco]

Thanks a bunch. I managed to do it my way, I feel this is better.

Declare @projectXml varchar(8000)
Declare @docHandle int

Set @projectXml = '<Root><Project projectid="1" /><Project projectid="3" /><Project projectid="4" /></Root>'

exec sp_xml_preparedocument @docHandle OUTPUT, @projectXml

Select pea.employeeId
From projectEmployeeAssignment pea
Inner join (
SELECT Distinct OX.projectid FROM OPENXML(@docHandle, N'/Root/Project') WITH (projectid int) OX
) xmlPro ON
xmlPro.projectId = pea.ProjectId
group by pea.employeeid
having count(pea.projectid) = (SELECT count(Distinct projectId) FROM OPENXML(@docHandle, N'/Root/Project') WITH (projectid int) OX)

EXEC sp_xml_removedocument @docHandle

I can create a Sproc out of this and take @projectXml as 'Text' type instead of varchar(8000) which should solve the 8000 character limitation.

[mendhak]

I created a table in my Northwind database just for you.

[chocoloco]

Well it is very much appreciated (if that makes you feel any better). In any case, now we have two different solutions to a problem.

I do not know why I was thinking this to be a easier thing, it wasn't really (not relatively anyway).

[chocoloco]

Was just looking at your solution ...

SELECT DISTINCT EmpID FROM EmpProjTemp WHERE dbo.GetProjectIDs(EmpID) = '123, 456, 789, 666'

wouldn't you still have to loop through 5000 times if there are 5000 users??

[MasterBlaster]

I can create a Sproc out of this and take @projectXml as 'Text' type instead of varchar(8000) which should solve the 8000 character limitation.

Sweet, that might work, i've never tried it using blobs.