CODEWITHSUNDEEP
sql-serversql-server-2008where-clause
amity2
amity2

Reputation: 27

Find records in a table based on condition using SQL

I have a table student_course as below:-

student course
 1        a
 2        a
 1        b
 4        a
 5        d
 2        d
 5        a

i need to find all the rows where course=a and course not in b. result should be:-

student course
 2        a
 4        a
 5        a

Upvotes: 1

Views: 111

Answers (6)

Guillermo Zooby
Guillermo Zooby

Reputation: 612

select * from student_course
where course = 'a' and student not in (select student from student_course where course = 'b')

Upvotes: 1

qxg
qxg

Reputation: 7036

Use LEFT JOIN.

SELECT T1.*
FROM student_course T1
LEFT JOIN student_course T2 ON T1.student = T2.student AND T2.course = 'B'
WHERE T1.course = 'A' AND T2.student IS NULL

Upvotes: 1

Paresh J
Paresh J

Reputation: 2419

SELECT * FROM student_course
WHERE course = 'a' AND student NOT IN 
(
 SELECT student FROM student_course a
 WHERE course = 'b' 
)

Upvotes: 1

user2321864
user2321864

Reputation: 2307

SELECT *
FROM student_course
WHERE course = 'a'
AND student NOT IN (SELECT student
                    FROM student_course
                    WHERE course = 'b');

Upvotes: 0

dotnetom
dotnetom

Reputation: 24901

Try this query:

SELECT ca.Student, ca.Course 
FROM student_course ca 
WHERE ca.course = 'a' 
    AND NOT EXISTS (SELECT 1 FROM student_course cb 
                    WHERE ca.Student = cb.Student AND cb.course = 'b' )

Upvotes: 0

juergen d
juergen d

Reputation: 204756

select student
from student_course 
group by student
having sum(case when course = 'a' then 1 else 0 end) > 0
and sum(case when course = 'b' then 1 else 0 end) = 0

Upvotes: 1

Related Questions