CODEWITHSUNDEEP
sql
KillerKidz
KillerKidz

Reputation: 180

SQL: Find common rows in different record

I have 3 tables:

I have a task which is, given two t_id, find the common students that these 2 teachers have taught.

Is it possible to accomplish this in strictly SQL? If not I might try to retrieve out the student records individually based on different teacher, and do a search to see which students they have in common. This seems a bit overkill for something that seems possible to write a SQL query for.

Upvotes: 0

Views: 77

Answers (2)

Ross Bush
Ross Bush

Reputation: 15175

You can self join to get students for both teachers.

DECLARE @TeacherID1 INT = 1
DECLARE @TeacherID2 INT = 2

SELECT 
    StudentID = T1.s_id,
    Teacher1 = T1.t_id,
    Teacher1ClassTime = T1.class_time ,
    Teacher2 = T2.t_id,
    Teacher2ClassTime = T2.class_time 
FROM 
    TeachingTable T1
    INNER JOIN TeachingTable T2 ON T2.s_id=T1._sid AND T2.t_id=@TeacherID2
WHERE 
    T1.t_id = @TeacherID1
ORDER BY
    T1.ClassTime

Upvotes: 2

Steven
Steven

Reputation: 15258

select s_id 
from student a
inner join teaching b on a.s_id = b.s_id
where t_id = 'First give t_id'
INTERSECT 
select s_id 
from student a
inner join teaching b on a.s_id = b.s_id
where t_id = 'Second give t_id'

This work with MS DB, but probably not with others. 

select s_id 
from student a
inner join teaching b on a.s_id = b.s_id
where b.t_id = 'First give t_id'
and s_id in (
select s_id 
from student c
inner join teaching d on c.s_id = d.s_id
where d.t_id = 'Second give t_id'
)

the second one should work with any DB.

Upvotes: 1

Related Questions