Forum Stats

  • 3,853,839 Users
  • 2,264,285 Discussions
  • 7,905,471 Comments

Discussions

Newbie: Simple select with 'IN ALL'

578885
578885 Member Posts: 93
edited Sep 22, 2007 7:42AM in SQL & PL/SQL
Hi..
I have a table PROJECT with data as
SQL> select person, id_state from project order by  1, 2;

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.
I want to select persons from this table having all the id states (2, 3 and 4)

In this case the result would be Mark.


The following quiery works
SQL> select person from project t3,
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
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.

Thanks all in advance..
«134

Comments

  • 465815
    465815 Member Posts: 544
    edited Aug 30, 2007 1:53AM
    try -
    select person, id_state from project where id_state in (2, 3, 4) order by  1, 2;
    Amiel
    oppes -misedd the part that syas all state...

    Message was edited by:
    Amiel D.
  • 569964
    569964 Member Posts: 278
    edited Aug 30, 2007 1:50AM
    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.

    I can't really think of a simpler way of doing it yet lol

    Isn't it annoying when you have a simple thing you want to do, but you can't seem to do it?

    Oracle is like that.

    -EDIT-

    hah, found a nifty way of doing it... it's a little bit dodgy, but it works!! :D
    select person
    from project
    where id_state in (2, 3, 4)
    group by person
    having count(*) = 3
    -EDIT 2-

    Hah, nice work Volder!

    I think you beat me =)
  • 572471
    572471 Member Posts: 984 Green Ribbon
    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> 
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Sep 22, 2007 7:42AM
    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;
    PERSON
    ------
    Mark
    Wendy
    I have used this Logic.
    http://en.wikipedia.org/wiki/First-order_logic
    "max(case when P(X) then 1 else 0 end) = 1"  is for some X:P(X)
    "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))
    therefore, in having clause I have used Boolean arithmetic (http://www.allaboutcircuits.com/vol_4/chpt_7/2.html)
    which "a And b And c And D" is "a * b * c * d = 1"


    similar threads
    551061
    550921
    526715
  • 569964
    569964 Member Posts: 278
    edited Aug 30, 2007 2:40AM
    Hello Aketi

    Unfortunately your query does not return the correct results...

    eg. John has 2, 3 and 4, yet he is not included in your results.

    Interesting though :)

    Knock out the last line and you'll get the right answer though.
    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
  • 576659
    576659 Member Posts: 205
    edited Aug 30, 2007 3:10AM
    select person from project where id_state in(2,3,4) group by person having count(person)=3

    thanks
    hari

    null
  • 569964
    569964 Member Posts: 278
    select person from project where id_state in(2,3,4) group by person having count=3
    Hehe, 3rd time that solution has been posted ^^
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Sep 2, 2007 5:48PM
    Unfortunately your query does not return the correct results...
    Wow!
    My English is poor.
    I misunderstood request.
    "min(case when ID_STATE in(2,3,4) then 1 else 0 end)" was extra.

    a
    max(case when ID_STATE = 2 then 1 else 0 end) = 1
    This means that "ID_STATE = 2" exists.

    b
    max(case when ID_STATE = 3 then 1 else 0 end) = 1
    This means that "ID_STATE = 3" exists.

    c
    max(case when ID_STATE = 4 then 1 else 0 end) = 1
    This means that "ID_STATE = 4" exists.

    d
    min(case when ID_STATE in(2,3,4) then 1 else 0 end) = 1
    This means that all rows are "ID_STATE in (2,3,4)".


    I understood that request was "a And b And c And d".
    But correct request was "a And b And c".
  • 576659
    576659 Member Posts: 205
    edited Aug 30, 2007 2:45AM
    i didnt see u posted there.

    Message was edited by:
    Hariharan M K
  • RAMJANE
    RAMJANE Member Posts: 317 Blue Ribbon
    Try this one

    select person,count(id_state) from project group by person having count(id_state)=3;

    This will give Mark as o/p.
This discussion has been closed.