SQL Select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Select

Author

Topic

sql4fun_2008
Starting Member

3 Posts

Posted - 2008-08-03 : 00:04:14

I am trying to learn SQL but I am having a problem coming with a query that will do the following:

Find the department(s) (dID) for which employees E1 (eID=‘E1’) and E2 (eID=‘E2’) both work.

Employees(eID (PK), dID, eName, eSalary)
Depts(dID(PK), dName, budget)

Any help will be greatly appreciated.

Thank you,
Paul

r937
Posting Yak Master

112 Posts

Posted - 2008-08-03 : 00:53:55

first you need a CROSS JOIN between employees and departments

that will make the employees angry but the departments will be tickled

then FULL OUTER JOIN the departments back to the budgets table

(if your homework assignment did not mention the budgets table, you should definitely put it into the query, as it will get you extra marks)

finally, add a WHERE clause for the E1 and E2 employees, being IN a subquery of departments

don't forget to throw in the GROUP BY clause and you're done

simple, innit

rudy
http://r937.com/

sql4fun_2008
Starting Member

3 Posts

Posted - 2008-08-03 : 10:18:30

Thanks Rudy,

I don't have a budgets table, if I do something like

select dID
from employees cross join depts
where e1.eid='E1' and e2.eid='E2'
GROUP by dID;

should give me the result.
Thanks again and please forgive me because I am still trying and totally understand this SQl business but would like to.

sql4fun_2008
Starting Member

3 Posts

Posted - 2008-08-03 : 12:13:28

Would something simple like this work:

SELECT E2.dID
FROM Employees e1, Employees e2
WHERE e1.dID=e2.dID AND e1.eID=`E1` AND e2.eID=`E2`;

Also in the question it is asking for department or departments should not it only be one department because I don't see how employees could belong to other departments.

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-03 : 13:42:53

quote:
Originally posted by sql4fun_2008

I am trying to learn SQL but I am having a problem coming with a query that will do the following:

Find the department(s) (dID) for which employees E1 (eID=‘E1’) and E2 (eID=‘E2’) both work.

Employees(eID (PK), dID, eName, eSalary)
Depts(dID(PK), dName, budget)

Any help will be greatly appreciated.

Thank you,
Paul

SELECT dName
FROM Depts d
INNER JOIN Employees e
ON e.dID=d.dID
GROUP BY dName
HAVING SUM(CASE WHEN eID='E1' OR eID='E2' THEN 1 ELSE 0 END) >=2
AND COUNT(DISTINCT eID)>=2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources