SQL Statement Same Column Different Data

Question

Hello, I have a table with students from 4 courses and two different departments (Math 101, Math 102, English 201, English 202). I want to select students from Math 101, and English 201 if they've had it and English 202 if they've had it or both if they've had them both.

---

Edit:

StudentID | Dept | CrsNum
 12345      MS     101
 99999      MS     102
 10000      EH     201
 56789      EH     201
 12345      EH     201
 12345      EH     202

Ideally, the query result would show the student 12345 with all courses he has been in since he is the only one who has been in both MS 101 and both of the EH courses.

Answer 1

SELECT S1.StudentID
  FROM Students AS S1 JOIN (SELECT S3.StudentID
                              FROM Students AS S3
                             WHERE S3.Dept = 'MS'
                               AND S3.CrsNum = 101) AS S2
       ON S1.StudentID = S2.StudentID
 WHERE S1.Dept = 'EH'
   AND S1.CrsNum IN (201, 202);

Or, if all the information is wanted (as suggested in the comment), use this as the sub-query that selects the relevant student IDs):

SELECT * FROM Students
 WHERE StudentID IN
            (SELECT S1.StudentID
               FROM Students AS S1 JOIN (SELECT S3.StudentID
                                           FROM Students AS S3
                                          WHERE S3.Dept = 'MS'
                                            AND S3.CrsNum = 101) AS S2
                    ON S1.StudentID = S2.StudentID
              WHERE S1.Dept = 'EH'
                AND S1.CrsNum IN (201, 202));

Answer 2

A simple sub-query will do:

select StudentID from table where 
(Dept = "MS" and CrsNum = 101) and 
StudentID in ( select StudentID from table where
             ( (Dept = "EH" and CrsNum = 201) or
               (Dept = "EH" and CrsNum = 202) ) )

Answer 3

Try something like:

select StudentID, Dept, CrsNum
from StudentCourse SC
where CrsNum in (101, 201, 202) and
exists 
(select null
 from StudentCourse SC1
 where SC.StudentID = SC1.StudentID and
       sc1.CrsNum = 101) and
exists 
(select null
 from StudentCourse SC2
 where SC.StudentID = SC2.StudentID and
       sc1.CrsNum in (201,202))

Alternatively, you should be able to do it without any subqueries with a query like:

select StudentID, 
       Sum(case when CrsNum = 101 then 1 else 0) InCrs101,
       Sum(case when CrsNum = 201 then 1 else 0) InCrs201,
       Sum(case when CrsNum = 202 then 1 else 0) InCrs202
from StudentCourse SC
where CrsNum in (101, 201, 202)
group by StudentID
having Sum(case when CrsNum = 101 then 1 else 0) >= 1 and
       (Sum(case when CrsNum = 201 then 1 else 0) >= 1 or
        Sum(case when CrsNum = 202 then 1 else 0) >= 1)