Selecting the same column twice in the same where clause

[Eric]

Hi :
From a crystal report i get a list of employee firstnames as a string
into my store procedure. Why is it comming this way ? hmmmmmm it's a
question for me too.
ex: "e1,e2,e3"

here are my tables

tblProjects
ProjectId
1
2
3

tblEmployee
employeeId FirstName
1 e1
2 e2
3 e3

tblProjectsToEmployee
ProjectId employeeId
1 1
1 2
1 3
2 1
2 2
3 1
3 2
3 3
4 1
4 3

i need to find out the project ids all 3 of these employees worked on.
so the out put i need is

projectId
1
3
How can i get it ????????????

now i can use replace command to format it to a OR clause or AND
clause
SET @string= 'employeeId =' + '''' + REPLACE('e1,e2,e3',',',''' OR
employeeId = ''') + ''''
some thing like this.

OR clause will give me all 4 projects.
in('e1','e2','e3') will give me all 4 projects.
of cause AND command will not give me any.

other method i tried was adding the employee table 3 times into the
same SQL string and doing some thing like

WHERE (empTable1.Firstname ='e1' AND (empTable1.Firstname
in('e2','e3'))
AND (empTable2.Firstname ='e2' AND (empTable1.Firstname in('e1','e3'))
AND ...

and goes alone. this gives me some what i needed. but it's a very
messy way of doing it, because i get a comma seperated string
parameter i have to construct the sql string on the fly.

any help or direction on this matter would greatly appreciated.

thanks
eric

[Hugo Kornelis]

On 7 Feb 2005 16:04:36 -0800, Eric wrote:

From a crystal report i get a list of employee firstnames as a string
into my store procedure. Why is it comming this way ? hmmmmmm it's a
question for me too.
ex: "e1,e2,e3" (snip)

Hi Eric,

The first step would be to convert the comma-delimited list of employees
to a temp table. SQL Server MVP Erland Sommarskog has a whole web page
devoted to various techniques to accomplish this. Check it out at
http://www.sommarskog.se/arrays-in-sql.html, pick one of the techniques
and implement it. For the rest of this message, I'll assume that the
employees you are looking for are not in a comma-delimited list anymore,
but in the temp table #Employees, column EmployeeID.

i need to find out the project ids all 3 of these employees worked on.
so the out put i need is

projectId
1
3
How can i get it ????????????

(snip)

This is called "relational division". The standard solution is

SELECT p.ProjectID
FROM Projects AS p
INNER JOIN ProjectsToEmployee AS pe
ON pe.ProjectID = p.ProjectID
INNER JOIN #Employees AS e
ON e.EmployeeID = pe.EmployeeID
GROUP BY p.ProjectID
HAVING COUNT(*) = (SELECT COUNT(*) FROM #Employees)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Joško Šugar]

Eric wrote:

Hi :
From a crystal report i get a list of employee firstnames as a string
into my store procedure. Why is it comming this way ? hmmmmmm it's a
question for me too.
ex: "e1,e2,e3"

here are my tables

tblProjects
ProjectId
1
2
3

tblEmployee
employeeId FirstName
1 e1
2 e2
3 e3

tblProjectsToEmployee
ProjectId employeeId
1 1
1 2
1 3
2 1
2 2
3 1
3 2
3 3
4 1
4 3

i need to find out the project ids all 3 of these employees worked on.
so the out put i need is

projectId
1
3
How can i get it ????????????

now i can use replace command to format it to a OR clause or AND
clause
SET @string= 'employeeId =' + '''' + REPLACE('e1,e2,e3',',',''' OR
employeeId = ''') + ''''
some thing like this.

OR clause will give me all 4 projects.
in('e1','e2','e3') will give me all 4 projects.
of cause AND command will not give me any.

other method i tried was adding the employee table 3 times into the
same SQL string and doing some thing like

WHERE (empTable1.Firstname ='e1' AND (empTable1.Firstname
in('e2','e3'))
AND (empTable2.Firstname ='e2' AND (empTable1.Firstname in('e1','e3'))
AND ...

and goes alone. this gives me some what i needed. but it's a very
messy way of doing it, because i get a comma seperated string
parameter i have to construct the sql string on the fly.

any help or direction on this matter would greatly appreciated.

thanks
eric

You can try this, I know database experts won't like this solution but
it works :)

SELECT ProjectId
FROM (SELECT tblProjects.ProjectId,SUM(tblEmployee.sadrzajid) as sum_all
FROM tblProjects
INNER JOIN tblProjectsToEmployee
ON tblProjects.ProjectId = tblProjectsToEmployee.ProjectId
INNER JOIN tblEmployee
ON tblProjectsToEmployee.employeeId = tblEmployee.employeeId
GROUP BY tblProjects.ProjectId) AS complet
WHERE (complet.sum_all = (SELECT SUM(employeeId)
FROM tblEmployee))

Josko

[Joško Šugar]

Joško Šugar wrote:

You can try this, I know database experts won't like this solution but
it works :)

SELECT ProjectId
FROM (SELECT tblProjects.ProjectId,SUM(tblEmployee.sadrzajid) as sum_all
FROM (SELECT tblProjects.ProjectId,SUM(tblEmployee.employeeId) as sum_all

lapsus, sorry
FROM tblProjects
INNER JOIN tblProjectsToEmployee
ON tblProjects.ProjectId = tblProjectsToEmployee.ProjectId
INNER JOIN tblEmployee
ON tblProjectsToEmployee.employeeId = tblEmployee.employeeId
GROUP BY tblProjects.ProjectId) AS complet
WHERE (complet.sum_all = (SELECT SUM(employeeId)
FROM tblEmployee))

Josko