1

STUDENT

StudentID | StudentName

1     |    'John'
2     |    'Mary'
3     |    'Steven'

REGISTRATION

StudentID | ClassNum

1      |  1000
1      |  1001
2      |  1000
3      |  2000
4      |  1000

I would like to only display studentID's that have ClassNum 1000 AND ClassNum 1001. They must have both to be printed out to show up. When I try to look up AND with WHERE statements, they usually have them asking two different columns.

When I try to look up AND with WHERE statements, they typically have them asking two different columns. I think this is because it is looking for a Student that has both at the same time, ClassNum 1000 AND ClassNum 1001, which isn't possible with this dataset. I want John to be the only one that shows up.

Ideal Result

 1 | 1000

 1 | 1001

Only display the student that has both class 1000 and 1001.

However, if SQL can do the following, that would be even better

1 | 1000 & 1001

That would be ideal, but I don't think that is possible. For now, only print a Student that has both

STUDENT(__StudentID, StudentName)
REGISTRATION(__StudentID, __ClassNum)

My code:

SELECT s.StudentName, r.ClassNum FROM STUDENT s
INNER JOIN REGISTRATION r ON r.StudentID = s.StudentID
WHERE ClassNum = '1000' AND ClassNum = '1001';

Nothing

Also played around with

SELECT s.StudentName, r.ClassNum FROM STUDENT s
INNER JOIN REGISTRATION r ON r.StudentID = s.StudentID
WHERE ClassNum IN ('1000', '1001');

It gives me all the students with any of them, but I want one that has both.

Tushar
  • 3,527
  • 9
  • 27
  • 49
User4u
  • 9
  • 2
  • Also see a couple of answers I have written to similar questions: https://stackoverflow.com/questions/7407001/mysql-select-ids-which-occur-on-different-rows-with-multiple-specific-values-fo/7407078#7407078 or https://stackoverflow.com/questions/24063021/in-sql-is-there-something-like-in-but-for-multiple-and-conditions/24111654#24111654. This operation is called [tag:relational-division]. – Bill Karwin Nov 22 '21 at 22:26

0 Answers0