CODEWITHSUNDEEP
sqlsql-server
user8400129
user8400129

Reputation: 213

How to SELECT value from a table in complex condition?

I have a table with 3 columns: course_name, year, semester.

Now I want to find out which subjects that have courses in each semester (S1 and S2) in each year (2001 and 2002).

I have tried for an hour writing CASE WHEN and GROUP BY HAVING but fail to get the correct result.

table_subjects:

course_name| year| semester
Programming   2001   S1
Programming   2001   S2
Programming   2002   S1
Programming   2002   S2
Law           2001   S1
Law           2001   S2
Law           2002   S2
Science       2001   S1
Science       2001   S2
Management    2002   S2
AI            2001   S1
Database      2001   S1
Database      2001   S2
Database      2002   S1
Database      2002   S2

Expected result:

|course_name|
 Programming
 Database

Upvotes: 1

Views: 84

Answers (2)

Chinthaka Fernando
Chinthaka Fernando

Reputation: 824

SELECT T.course_name
FROM
(
    SELECT course_name
    FROM table_subjects
    GROUP BY course_name,
             year
    HAVING COUNT(1) = 2
) T
GROUP BY T.course_name
HAVING COUNT(1) = 2;

Upvotes: 0

Fahmi
Fahmi

Reputation: 37483

You can try below -

DEMO

select course_name
from t1 a
where year in (2001,2002) and exists (select 1 from t1 b where a.course_name=b.course_name
and a.year=b.year and semester in ('S1','S2') having count(distinct semester)=2)
group by course_name
having count(distinct year)=2

OUTPUT:

course_name
Database
Programming

Upvotes: 3

Related Questions