This discussion is archived
1 2 3 Previous Next 31 Replies Latest reply: Sep 2, 2007 11:11 PM by William Robertson RSS

Newbie: Simple select with 'IN ALL'

578885 Newbie
Currently Being Moderated
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..
  • 1. Re: Newbie: Simple select with 'IN ALL'
    465815 Journeyer
    Currently Being Moderated
    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.
  • 2. Re: Newbie: Simple select with 'IN ALL'
    569964 Newbie
    Currently Being Moderated
    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 =)
  • 3. Re: Newbie: Simple select with 'IN ALL'
    572471 Newbie
    Currently Being Moderated
    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> 
  • 4. Re: Newbie: Simple select with 'IN ALL'
    Aketi Jyuuzou Oracle ACE
    Currently Being Moderated
    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
    How can I apply query for criteria?
    simple select?
    Help Needed to write select statement
  • 5. Re: Newbie: Simple select with 'IN ALL'
    569964 Newbie
    Currently Being Moderated
    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
  • 6. Re: Newbie: Simple select with 'IN ALL'
    576659 Newbie
    Currently Being Moderated
    select person from project where id_state in(2,3,4) group by person having count(person)=3

    thanks
    hari

    null
  • 7. Re: Newbie: Simple select with 'IN ALL'
    569964 Newbie
    Currently Being Moderated
    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 ^^
  • 8. Re: Newbie: Simple select with 'IN ALL'
    Aketi Jyuuzou Oracle ACE
    Currently Being Moderated
    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".
  • 9. Re: Newbie: Simple select with 'IN ALL'
    576659 Newbie
    Currently Being Moderated
    i didnt see u posted there.

    Message was edited by:
    Hariharan M K
  • 10. Re: Newbie: Simple select with 'IN ALL'
    586196 Newbie
    Currently Being Moderated
    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.
  • 11. Re: Newbie: Simple select with 'IN ALL'
    569964 Newbie
    Currently Being Moderated
    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.
  • 12. Re: Newbie: Simple select with 'IN ALL'
    586196 Newbie
    Currently Being Moderated
    ya sorry for my mistake.

    This is the latest query for doing this

    select person,count(case when id_state=2 then id_state when id_state=3 then id_state when id_state=4 then id_state end) as count1 from project group by person having count1=3;

    Try this one.
  • 13. Re: Newbie: Simple select with 'IN ALL'
    569964 Newbie
    Currently Being Moderated
    This is probably the shortest query though:
    select person from project
    where id_state in (2, 3, 4)
    group by person
    having count(*) = 3
  • 14. Re: Newbie: Simple select with 'IN ALL'
    586196 Newbie
    Currently Being Moderated
    you are right. What i was thinking that in case of IN it will consider all others also. Now it's time to recall all the stuff for me.
1 2 3 Previous Next