SQL> select person, id_state from project order by 1, 2;I want to select persons from this table having all the id states (2, 3 and 4)
PERSON ID_STATE
---------- ----------
Carl 3
Carl 4
Joe 3
Mark 2
Mark 3
Mark 4
Paul 1
Paul 2
Sam 2
Sam 3
10 rows selected.
SQL> select person from project t3,But I think this is too large a quiery for such a simple select. Can any one post a more refined and compact quiery for this simple select.
2 (
3 select person p2, id_state from project t2,
4 (
5 select person p1 from PROJECT where ID_STATE = 2
6 ) t1
7 where t1. p1 = t2.person and t2.id_state = 3
8 ) t4
9 where t3.person = t4.p2 and t3.id_state = 4
10 /
PERSON
----------
Mark
select person, id_state from project where id_state in (2, 3, 4) order by 1, 2;Amiel
select person, id_state from project where id_state in (2, 3, 4) order by 1, 2;Nah, this returns all of the records, not just the ones with 2, 3 and 4.
select person
from project
where id_state in (2, 3, 4)
group by person
having count(*) = 3
-EDIT 2-SQL> with t as (select 'Carl' PERSON, 3 ID_STATE from dual union all
2 select 'Carl' PERSON, 4 ID_STATE from dual union all
3 select 'Joe' PERSON, 3 ID_STATE from dual union all
4 select 'Mark' PERSON, 2 ID_STATE from dual union all
5 select 'Mark' PERSON, 3 ID_STATE from dual union all
6 select 'Mark' PERSON, 4 ID_STATE from dual union all
7 select 'Paul' PERSON, 1 ID_STATE from dual union all
8 select 'Paul' PERSON, 2 ID_STATE from dual union all
9 select 'Sam' PERSON, 2 ID_STATE from dual union all
10 select 'Sam' PERSON, 3 ID_STATE from dual)
11 --
12 select person from t
13 where id_state in (2,3,4)
14 group by person
15 having count(distinct id_state)=3
16 /
PERSON
------
Mark
SQL>
with t as (select 'Carl' as PERSON,3 as ID_STATE from dual
union all select 'Carl',4 from dual
union all select 'Joe' ,3 from dual
union all select 'Mark',2 from dual
union all select 'Mark',3 from dual
union all select 'Mark',4 from dual
union all select 'Paul',1 from dual
union all select 'Paul',2 from dual
union all select 'Sam' ,2 from dual
union all select 'Sam' ,3 from dual
union all select 'Wendy' ,2 from dual
union all select 'Wendy' ,2 from dual
union all select 'Wendy' ,3 from dual
union all select 'Wendy' ,3 from dual
union all select 'Wendy' ,4 from dual
union all select 'John' ,1 from dual
union all select 'John' ,2 from dual
union all select 'John' ,2 from dual
union all select 'John' ,3 from dual
union all select 'Tom' ,1 from dual
union all select 'Tom' ,2 from dual
union all select 'Tom' ,3 from dual
union all select 'Tom' ,4 from dual)
select person
from t
group by person
having max(case when ID_STATE = 2 then 1 else 0 end)
* max(case when ID_STATE = 3 then 1 else 0 end)
* max(case when ID_STATE = 4 then 1 else 0 end)
* min(case when ID_STATE in(2,3,4) then 1 else 0 end) = 1;
PERSONI have used this Logic.
------
Mark
Wendy
"max(case when P(X) then 1 else 0 end) = 1" is for some X:P(X)therefore, in having clause I have used Boolean arithmetic (http://www.allaboutcircuits.com/vol_4/chpt_7/2.html)
"min(case when P(X) then 1 else 0 end) = 1" is for all X:P(X)
"max(case when P(X) then 0 else 1 end) = 1" is for some X:not(P(X))
"min(case when P(X) then 0 else 1 end) = 1" is for all X:not(P(X))
select person
from project
group by person
having max(case when ID_STATE = 2 then 1 else 0 end)
* max(case when ID_STATE = 3 then 1 else 0 end)
* max(case when ID_STATE = 4 then 1 else 0 end) = 1
or similarly:select person
from project
group by person
having max(case when ID_STATE = 2 then 1 else 0 end) = 1
and max(case when ID_STATE = 3 then 1 else 0 end) = 1
and max(case when ID_STATE = 4 then 1 else 0 end) = 1
select person from project where id_state in(2,3,4) group by person having count=3Hehe, 3rd time that solution has been posted ^^
Unfortunately your query does not return the correct results...Wow!
select person,count(id_state) from project group by person having count(id_state)=3;Problem is that this will select any people with exactly 3 rows, so someone with 1, 2, 6 would also be in.
select person from project
where id_state in (2, 3, 4)
group by person
having count(*) = 3