CODEWITHSUNDEEP
sqlmysql
phildo
phildo

Reputation: 33

SQL Select Statement

I think this is a pretty basic question and I have looked around on the site but I am not sure what to search on to find the answer.

I have an SQL table that looks like: 

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

I am looking for a SELECT statement that finds the studentId that is taking math 1st period and english 2nd period. I have tried something like SELECT studentID WHERE ( period = 1 AND class= "math" ) AND ( period = 2 AND class = "english" ) but that has not worked.

I have also thought about changing my table to be: 

studentId  period1  period2  period3  period4  period5 etc

But I think I want to be adding things besides classes like after school activities and wanted to be able to expand easily without constantly having to add columns.

Thanks for any help you can give me.

Upvotes: 3

Views: 149

Answers (3)

David
David

Reputation: 73554

You can use subqueries to do each individually and get only results where both subqueries match.

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" ) )

Edit - added

I have not tested this myself, but I was curious about performance considerations, so I looked it up. I found this quote:

Many Transact-SQL statements that include subqueries can be alternatively formulated as joins. Other questions can be posed only with subqueries. In Transact-SQL, there is usually no performance difference between a statement that includes a subquery and a semantically equivalent version that does not. However, in some cases where existence must be checked, a join yields better performance. Otherwise, the nested query must be processed for each result of the outer query to ensure elimination of duplicates. In such cases, a join approach would yield better results. The following is an example showing both a subquery SELECT and a join SELECT that return the same result set:

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

Upvotes: 2

HLGEM
HLGEM

Reputation: 96552

You could also do a self join

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" )

Upvotes: 2

The Surrican
The Surrican

Reputation: 29856

try something like:

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

the idea is to select all who meet the first criteria or the second criteria, group it by person and see where all are met by checking the number of rows grouped

Upvotes: 3

Related Questions