SQL Select Output

Question

I think this is a fairly simple question, and I looked around the site, but I'm not sure what to look for to find the answer.

I have an SQL table that looks like this:

studentId  period  class  
1          1       math  
1          2       english  
2          1       math  
2          2       history

I am looking for a SELECT statement that finds studentId that takes a mathematical 1st period and an English 2nd period. I tried something like SELECT studentID WHERE ( period = 1 AND class= "math" ) AND ( period = 2 AND class = "english" ), but that didn't work.

I also thought about changing the table:

studentId  period1  period2  period3  period4  period5 etc  

But I think that I want to add things besides classes, for example, after classes at school, and would like to be able to expand easily, without the need to add columns.

Thanks for any help you can give me.

Answer 1

, .

Select StudentId FROM table WHERE 
    StudentId IN 
       (SELECT studentID FROM table WHERE ( period = 1 AND class= "math" ) ) 
AND 
    StudentId IN 
       (SELECT studentID FROM table WHERE ( period = 2 AND class= "english" ) ) 

-

, , . :

Transact-SQL, . . Transact-SQL , , . , , . . , . , SELECT SELECT, :

: http://technet.microsoft.com/en-us/library/ms189575.aspx

Answer 2

- :

select studentid from table where ( period = 1 AND class= "math" ) or ( period = 2 AND class = 
"english" ) group by studentid having count(*) >= 2

, , , , ,

Answer 3

You can also do it yourself

SELECT  t1.studentID  
FROM table t1
JOIN table t2 ON t1.studentID = t2.studentID   
WHERE ( t1.period = 1 AND t1.class= "math" )    
  AND ( t2.period = 2 AND t2.class = "english" )