CODEWITHSUNDEEP
sqlsql-server
user8400129
user8400129

Reputation: 213

How to SELECT a value in a 'same' condition?

I'm trying to using SQL query to find out how many students have study 2 subjects in the same semester.

I have a table which have 3 rows, and I want to find out which student have studied both subject '4877' and '4897' at the same semester. But I don't know how to describe 'in the same semester'.

student subject semester
1070781 4877    126
1070781 4897    126
1076677 4877    135
1078595 4877    126
1078595 4897    126
1078716 4897    127
1079496 4877    126
1079496 4897    126
1079627 4897    127
1080640 4877    127
...

SELECT student 
FROM table 
WHERE "studied 4877 and 4897 in the same semester";

student
1070781
1078595 
1079496 
...

Upvotes: 2

Views: 77

Answers (3)

Radim Bača
Radim Bača

Reputation: 10701

Another solution is to use CROSS APPLY

SELECT DISTINCT studentid, semester
FROM yourtable a1
CROSS APPLY (
  SELECT count(distinct a2.subject ) c1
  FROM yourtable a2
  WHERE subject  in (1, 500) and
        a2.studentid = a1.studentid and
        a2.semester = a1.semester
) t
WHERE t.c1 = 2

Upvotes: 0

Venkataraman R
Venkataraman R

Reputation: 12959

The below query is tested in SQL Server. 

 CREATE TABLE #students
(
student bigint,
subject int,
semester int
)
INSERT INTO #students VALUES
(1070781,4877,126),
(1070781,4897,126);

SELECT student
from
(SELECT semester, student, SUM(CASE WHEN subject IN ('4877','4897') THEN 1 ELSE 0 END) as StudiedTwo
from #Students
group by semester, student
) as t
where StudiedTwo = 2

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use this query:

SELECT studentid, semester
FROM mytable
WHERE subject IN (4877, 4897)
GROUP BY studentid, semester
HAVING COUNT(DISTINCT subject) = 2

The query returns all student id's having attended both subjects in at least one semester.

Note: The HAVING clause uses COUNT DISTINCT instead of a simple COUNT so as to avoid returning false positive records like in the following case:

student subject semester
------------------------
1070781 4877    126
1070781 4877    126

Upvotes: 6

Related Questions