CODEWITHSUNDEEP
sql
Perse-Fisher
Perse-Fisher

Reputation: 23

SQL two WHERE conditions for the same column

i have tried to get Essn values from the table below from only those who work in Pno 3 but not in 10

So the result woud be "666884444"

I tried to do it like 

SELECT Essn
FROM WORKS_ON 
WHERE Pno = 3 and Pno != 10;

But cant get the right result

WORKS_ON

---Essn-----------Pno-----------Hours----
"123456789"       "1"           "32.5"
"123456789"       "2"           "7.5"
"666884444"       "3"           "40"
"453453453"       "1"           "20"
"453453453"       "2"           "20"
"333445555"       "2"           "10"
"333445555"       "3"           "10"
"333445555"      "10"           "10"
"333445555"      "20"           "10"
"999887777"      "30"           "30"
"999887777"      "10"           "10"
"987987987"      "10"           "35"
"987987987"      "30"           "5"
"987654321"      "30"           "20"
"987654321"      "20"           "15"
"888665555"      "20"           NULL

Upvotes: 0

Views: 78

Answers (3)

Suresh Kumar
Suresh Kumar

Reputation: 675

SELECT Essn
FROM WORKS_ON 
WHERE Pno = "3"

This itself returns Essn of only the Pno column has value of 3.

Why do you need another WHERE Pno != 10.

Upvotes: 0

RoundFour
RoundFour

Reputation: 347

Your WHERE-Condition doesn't work because it's only comparing the values of each row separately. There are many ways to achieve this, this is my first idea:

SELECT DISTINCT Essn
FROM WORKS_ON 
WHERE Pno = 3 AND Essn NOT IN (SELECT Essn FROM WORKS_ON WHERE Pno = 10);

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I think you an do what you want with group by and having:

SELECT Essn
FROM WORKS_ON 
GROUP BY Essn
HAVING SUM(CASE WHEN Pno = 3 THEN 1 ELSE 0 END) > 0 AND   -- has 3
       SUM(CASE WHEN Pno = 10 THEN 1 ELSE 0 END) = 0;     -- does not have 10

Upvotes: 0

Related Questions