Home > IT Answers > SQL Server > SQL Select Stmt
testaduro
55 pts.
 SQL Select Stmt
SQL Select
Need to create a sql stmt to find all the people who work on exactly the same projects and same hours as Smith.

Software/Hardware used:
sql 2008
ASKED: October 26, 2012  1:56 AM
RELATED QUESTIONS
Answer Wiki:
Just a single table with 3 columns (name,project,hours), the following works fine, but is there a better way to right this? SELECT DISTINCT name FROM WorksOn a WHERE NOT EXISTS   (SELECT * FROM WorksOn b    WHERE b.name = 'Smith' AND NOT EXISTS       (SELECT * FROM WorksOn c       WHERE a.name = c.name AND       c.project = b.project AND c.hours = b.hours))
Last Wiki Answer Submitted:  October 26, 2012  4:41 am  by  testaduro   55 pts.
All Answer Wiki Contributors:  testaduro   55 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

What problem are having with doing that? — Tom

TomLiotta
 107,845 pts.
 

Not really a problem, just looking for an alternative to an existing stmt that I have created.

testaduro
 55 pts.
 

We need to know the table formats that are used. Your existing statement would also be a real help. — Tom

TomLiotta
 107,845 pts.
 

Can you explain why you are using those ‘NOT EXISTS’ conditions ?

carlosdl
 63,535 pts.
 

Maybeselect * from WorksOn a join
(select * from WorksOn where name = ‘Smith’) b
on a.project = b.project AND a.hours = b.hours and a.name <> b.name

msi77
 1,610 pts.