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

Question

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.

Self join should work. quick and easy.

ViKiNG
– ViKiNG

2018-01-10 02:42:42 +00:00
Commented Jan 10, 2018 at 2:42 — ViKiNG
– ViKiNG, Commented Jan 10, 2018 at 2:42

Gordon Linoff · Accepted Answer · 2018-01-10 02:56:39Z

2

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');

edited Jan 10, 2018 at 2:56

answered Jan 10, 2018 at 2:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mike Tung Over a year ago

this has a lot of aggregations?

Gordon Linoff Over a year ago

@VamsiPrabhala . . . "Dave" teaches all the classes that Dave teaches: "I'm trying to select those people who also teach the same classes as Dave". The OP, of course, can filter out Dave if s/he so chooses.

Pரதீப் · Accepted Answer · 2018-01-10 03:13:01Z

0

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

answered Jan 10, 2018 at 3:13

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Comments

Abdul Rasheed · Accepted Answer · 2018-01-10 06:01:28Z

0

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

answered Jan 10, 2018 at 6:01

Abdul Rasheed

6,7794 gold badges36 silver badges50 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related