CODEWITHSUNDEEP
mysqlsql
cppcoder
cppcoder

Reputation: 23145

Retrieve records satisfying a condition and not satisfying another condition

I have a table named tuition
I want to retrieve all the students who study 111 and who does not study 333
This is what I can think of:

http://www.sqlfiddle.com/#!2/f6411/3

stud_id | subject_id
--------------------
    1       111
    1       222
    2       222
    2       333
    3       111
    3       222
    3       333
    4       111
    4       222

Output: 

stud_id
-------
   1
   4

Upvotes: 0

Views: 68

Answers (4)

Raging Bull
Raging Bull

Reputation: 18767

Three alternatives:

  1. Using HAVING clause and WHERE clause:

    SELECT *
FROM tution 
WHERE subject_id = 111 OR subject_id=333
GROUP BY stud_id
HAVING COUNT(DISTINCT subject_id)=1 AND subject_id<>333

    Result in SQL Fiddle.

  2. Using HAVING clause without WHERE clause:

    SELECT *
FROM tution 
GROUP BY stud_id
HAVING COUNT(DISTINCT subject_id)=2 AND subject_id =111 AND subject_id<>333

    Result in SQL Fiddle.

  3. Using IN:

    SELECT *
FROM tution
WHERE subject_id=111
AND stud_id NOT IN (SELECT stud_id FROM tution WHERE subject_id= 333)

    Result in SQL Fiddle.

Result:

STUD_ID  SUBJECT_ID
1        111
4        111

Upvotes: 2

semao
semao

Reputation: 1757

Try this:

SELECT t1.stud_id FROM tuition t1 
WHERE t1.subject_id=111 
AND NOT EXISTS(SELECT 1 FROM tuition t2 
               WHERE t1.stud_id=t2.stud_id 
               AND t2.subject_id = 333)

Upvotes: 0

G one
G one

Reputation: 2729

select stud_id from tution 
where stud_id not in (select stud_id from tution t 
                  where t.subject_id = 333) 
and subject_id = 111

Fiddle

Upvotes: 1

Sarfaraz Makandar
Sarfaraz Makandar

Reputation: 6753

The following show you all distinct student who satisfied given condition. 

SELECT Distinct stud_id
FROM tution
WHERE subject_id=111
AND 
stud_id NOT IN (SELECT stud_id FROM tution WHERE subject_id= 333)

Upvotes: 0

Related Questions