1 2 3 Previous Next 31 Replies Latest reply: Sep 3, 2007 1:11 AM by William Robertson RSS

    Newbie: Simple select with 'IN ALL'

    578885
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          i didnt see u posted there.

                          Message was edited by:
                          Hariharan M K
                          • 10. Re: Newbie: Simple select with 'IN ALL'
                            RAMJANE
                            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
                              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'
                                RAMJANE
                                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
                                  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'
                                    RAMJANE
                                    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