CODEWITHSUNDEEP
sqlsql-serverselect
user3529379
user3529379

Reputation: 179

SQL - How to select rows which have the same multiple values

I'll start with a simplified example of my table:

+-----+----------+
|Name |Teaches   |
+-----+----------+  
|Dave |Science   |
+-----+----------+  
|Dave |History   |
+-----+----------+  
|Alice|History   |
+-----+----------+  
|Alice|Literature|
+-----+----------+  
|Alice|Science   |
+-----+----------+  
|John |History   |
+-----+----------+

I'm trying to select those people who also teach the same classes as Dave. (In this case, Alice). I'm thinking of using a cursor to go through Dave's courses and selecting those people who teach the same course and intersecting the results, but I'd like to know if there is a better (simpler) way.

Upvotes: 7

Views: 136

Answers (3)

Abdul Rasheed
Abdul Rasheed

Reputation: 6709

One method, used CTE here.

;WITH CTE_Parent
AS  (
    SELECT  Name,Teaches,COUNT(*) OVER() AS Parent_Count
    FROM    @Teachers
    WHERE   Name    =   'Dave'
)

SELECT  T.Name
FROM    @Teachers   AS  T
    INNER JOIN  CTE_Parent  AS  C   ON  C.Name  <>  T.Name
                                    AND C.Teaches=  T.Teaches
GROUP BY T.Name,C.Parent_Count
HAVING  COUNT(*)    =   C.Parent_Count

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

You need to use Self join, something like this 

SELECT a.NAME
FROM   Table1 a
       INNER JOIN (SELECT Teaches,
                          Count(*)OVER() AS cnt
                   FROM   Table1
                   WHERE  NAME = 'Dave') b
               ON a.Teaches = b.Teaches
WHERE  a.NAME <> 'Dave'
GROUP  BY a.NAME,
          b.cnt
HAVING Count(*) = b.cnt

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269483

Here is one method:

select t.name
from t join
     t td
     on td.teaches = t.teaches 
where td.name = 'Dave'
group by t.name
having count(*) = (select count(*) from t where t.name = 'Dave');

Upvotes: 2

Related Questions