SQL - Finding if a single value meets criteria from one column

Question

I have these two groups of data

CNU       RENT       NBED   CATEGORY
--- ---------- ---------- ----------
101        200          2          3
102        220          3          3
103        180          2          2
104        120          1          1
105        300          3          4
106        350          3          4
107        360          3          4
108        400          4          4
109        500          3          5
110        600          4          5



AID EID CNU SID ADATE          HOURS
--- --- --- --- --------- ----------
a01 e08 101 s02 15-MAY-14          3
a02 e03 101 s03 16-MAY-14          4
a03 e02 102 s02 17-MAY-14          2
a04 e09 103 s01 14-MAY-14          3
a05 e06 105 s05 18-MAY-14          5
a06 e06 107 s04 15-MAY-14          3
a07 e03 108 s04 18-MAY-14          4
a08 e09 109 s01 20-MAY-14          5
a09 e08 106 s06 15-MAY-14          3
a10 e10 102 s04 15-MAY-14          2
a11 e02 110 s04 15-MAY-14          4
a12 e10 103 s05 15-MAY-14          5
a13 e05 105 s04 16-MAY-14          4
a14 e10 107 s04 17-MAY-14          3
a15 e09 110 s01 18-MAY-14          3

I want to be able to display the EID of those whom are category 3 AND 4.

so the output would look like this

EID
---
e03
e08
e10
e02

This is the code i have used so far

SELECT EID 
FROM Assignment15
JOIN Cottage15
ON Assignment15.Cnum = Cottage15.Cnum
WHERE Category=3  
AND EXISTS (SELECT Category
            FROM Cottage15
            WHERE Category=Cottage15.Category
            AND Category=4);

and the result gives me one extra EID

EID
---
e03
e08
e10
e02

e02 being the extra. I'm not sure what can be adjusted, But it would be a great ton of help if i could get a code that works for this. Thank you very much in advance

Answer 1

You can simply use the IN keyword :

SELECT EID 
 FROM Assignment15
 JOIN Cottage15 ON Assignment15.Cnum = Cottage15.Cnum
 WHERE Category IN (3,4) 
 GROUP BY EID
 HAVING COUNT(DISTINCT Category) = 2

Note that on some DBMS you can also use the USING keyword on the JOIN condition which would result in

SELECT EID 
 FROM Assignment15
 JOIN Cottage15 ON USING(Cnum)
 WHERE Category IN (3,4) 
 GROUP BY EID
 HAVING COUNT(DISTINCT Category) = 2

Answer 2

You can approach this using aggregation and having:

select a.eid
from Assignment15 a join
     Cottage15 c
     on a.Cnum = c.Cnum
where c.Category in (3, 4)
group by a.eid
having count(distinct c.category) = 2 ;