50 Query Series-the sixth query: The query learned "001" and also learned the number "002" course of the students of the school number, name

Inquiry learned "001" and also learned the number "002" course of the students ' student number, name:

My wording:

First step: First find out to learn the "001" and also learned the number of "002" Students of the class number:

SELECTT11.t1sid t33id from (SELECTT1. Stuid T1sid fromTblscore T1WHERET1. CourseID='001') T11,--001 of the student's ID(SELECTT1. Stuid T2sid fromTblscore T1WHERET1. CourseID='002') t22--002 of the student's IDWHERET11.t1sid=T22.t2sid

The result of the query is:

The second step: according to the student's ID to check the student's name, study number

SELECTtblstudent. Stuid,tblstudent. Stuname from(SELECTT11.t1sid t33id from (SELECTT1. Stuid T1sid fromTblscore T1WHERET1. CourseID='001') T11,--001 of the student's ID(SELECTT1. Stuid T2sid fromTblscore T1WHERET1. CourseID='002') t22--002 of the student's IDWHERET11.t1sid=t22.t2sid) T33--Find out is learning "001" and also learned the number of "002" Students of the class number, TblstudentWHERETblstudent. Stuid=T33.t33id

Query Result:

The answer is:

 SELECTStuid,stuname fromTblstudent StWHERE(SELECT COUNT(*) fromTblscore S1WHERES1. Stuid=St. Stuid andS1. CourseID='001')>0    and   (SELECT COUNT(*) fromTblscore S2WHERES2. Stuid=St. Stuid andS2. CourseID='002')>0

Let's take a look at some of this notation:

His main watch is the student table (tblstudent)

COUNT (WHERE s1. Stuid andS1. CourseID='001'     
This sentence is said to traverse tblstudent each data and tblscore inside each piece of data, requires tblstudent Stuid and Tblscore data consistent:
In this case, the candidate is:

And then

S1. CourseID='001'     So one thing: there's only a single piece of data left.
If you meet

COUNT (WHERE s1. Stuid andS1. CourseID='001') >0       can be queried to show that it satisfies a 001 course.
  Similarly

(COUNT (WHERE S2. Stuid andS2. CourseID='002')>0 can be queried to show that it satisfies a 002 course.

The students who meet both conditions at the same time are students who have had two courses at the same time.

50 Query Series-the sixth query: The query learned "001" and also learned the number "002" course of the students of the school number, name