CODEWITHSUNDEEP
sqlselect
Tim Spencer
Tim Spencer

Reputation: 131

SQL - Filtering result to match TWO columns in TWO different rows

I'm working on a SQL quiz as follows: Write an SQL Statement to retrieve all the people who work on the same projects as smith with the same amount of hours with respect to each Project. With the sample data, only Smith and Brown should be retrieved. Oaks is disqualified since Oaks has worked on Project Y for 10 hours only (instead of 20 as smith has)

The table: 

|  name | project | hours |
|-------|---------|-------|
| Smith |       X |    10 |
| Smith |       Y |    20 |
|   Doe |       Y |    20 |
| Brown |       X |    10 |
|   Doe |       Z |    30 |
| Chang |       X |    10 |
| Brown |       Y |    20 |
| Brown |       A |    10 |
| Woody |       X |    10 |
| Woody |       Y |    10 |

I came up with this:

SELECT * INTO #temp 
FROM workson 
WHERE name='smith'
SELECT * from workson as w 
WHERE project IN 
  (SELECT project FROM #temp
  WHERE project=w.project AND hours=w.hours )
DROP TABLE #temp

Results:

name    project hours
Smith   X       10
Smith   Y       20
Doe     Y       20
Brown   X       10
Chang   X       10
Brown   Y       20
Woody   X       10

But the question expects only Smith and Brown to be returned. I can't figure out how to filter the others out in any kind of elegant way.

Thanks.

Upvotes: 2

Views: 58

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

This solution is an alternative to the accepted answer. It uses two INNER JOIN operations to achieve the final result. The SELECT subquery identifies names which have the same project/hour sets as 'Smith'.

SELECT t2.name, t1.project, t1.hours
FROM workson t1
INNER JOIN workson t2
     ON t1.project = t2.project AND t1.hours = t2.hours
INNER JOIN
(
    SELECT w2.name
    FROM workson w1
    INNER JOIN workson w2
        ON w1.project = w2.project AND w1.hours = w2.hours
    WHERE w1.name = 'Smith'
    GROUP BY w2.name
    HAVING COUNT(*) = (SELECT COUNT(*) FROM workson WHERE name = 'Smith')
) t3
    ON t2.name = t3.name
WHERE t1.name = 'Smith'

Follow the link below for a running demo:

SQLFiddle

Upvotes: 0

Stidgeon
Stidgeon

Reputation: 2723

I had some problems with the above answer, but it gave me a very good framework so I can't really take credit for this answer:

SELECT name, project, hours FROM workson w2
WHERE name IN 
(SELECT name FROM workson w
INNER JOIN 
(SELECT project, hours FROM workson
WHERE name = 'Smith') q1
ON q1.project = w.project AND q1.hours = w.hours
GROUP BY w.name
HAVING COUNT (*) = (SELECT COUNT(*) FROM workson WHERE name = 'Smith'))
AND project IN (SELECT project FROM workson WHERE name = 'Smith')

Upvotes: 1

daniel
daniel

Reputation: 1070

select t1.*
from workson t1
inner join workson t2 on t2.name = 'Smith' and t2.project = t1.project and t2.hours = t1.hours
where t1.name in
(
  select i1.name
  from workson i1
  inner join workson i2 on i2.name = 'Smith' and i2.project = i1.project and i2.hours = i1.hours
  group by i1.name
  having count(*) = (select count(*) from workson where name = 'Smith')
)

http://sqlfiddle.com/#!3/74566/2/0

Upvotes: 2

Related Questions