select - SQL - Finding if a single value meets criteria from one column -
this question has answer here:
i have these 2 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 able display eid of whom category 3 , 4.
so output this
eid --- e03 e08 e10 e02 this code have used far
select eid assignment15 join cottage15 on assignment15.cnum = cottage15.cnum category=3 , exists (select category cottage15 category=cottage15.category , category=4); and result gives me 1 eid
eid --- e03 e08 e10 e02 e02 being extra. i'm not sure can adjusted, great ton of if code works this. thank in advance
you can use in keyword :
select eid assignment15 join cottage15 on assignment15.cnum = cottage15.cnum category in (3,4) group eid having count(distinct category) = 2 note on dbms can use using keyword on join condition result in
select eid assignment15 join cottage15 on using(cnum) category in (3,4) group eid having count(distinct category) = 2