1 2 3 Previous Next 31 Replies Latest reply: Sep 3, 2007 6:11 AM by William Robertson Go to original post RSS
      • 15. Re: Newbie: Simple select with 'IN ALL'
        572471
        This is probably the shortest query though:
        but would give wrong result in case duplicate records exist in the table!
        • 16. Re: Newbie: Simple select with 'IN ALL'
          RAMJANE
          This is probably the shortest query though:
          but would give wrong result in case duplicate records
          exist in the table!
          but would give wrong result in case duplicate records
          exist in the table!
          What do you mean by duplicate records in table?
          any example.
          • 17. Re: Newbie: Simple select with 'IN ALL'
            578885
            Hi,
            lets suppose we have data as
            SQL> select * from PROJECT6030 order by 2;

            ID_PROJECT PERSON       ID_STATE LAST_UPDA
            ---------- ---------- ---------- ---------
                     6 Carl                4 22-JAN-05
                     5 Carl                3 08-DEC-04
                     1 Joe                 3 08-AUG-07
                     2 Mark                2 05-JUL-07
                     4 Mark                4 14-MAR-07
                     3 Mark 3 27-JUN-07
                     8 Mark                3 10-MAY-07
                     8 Paul                1 30-JUL-07
                     9 Paul                2 02-JUN-06
                     7 Sam                 3 11-JUL-06
                     7 Sam                 2 15-JUN-06

            11 rows selected.
            In this case our quiery should select Mark as Mark has all three Id states.

            Now consider the quiery.
            SQL> ed
            Wrote file afiedt.buf

              1  select person from project6030
              2  where id_state in (2, 3, 4)
              3  group by person
              4* having count(*) = 3
              5  /

            no rows selected
            when you group by count(*) we see that no person is having 3 rows of data.

            now lets see another quiery which Volder had posted in the begining.
            SQL> ed
            Wrote file afiedt.buf

              1  select person from project6030
              2  where id_state in (2, 3, 4)
              3  group by person
              4* having count(distinct id_state) = 3
            SQL> /

            PERSON
            ----------
            Mark
            Thank you all for your responses!!
            • 18. Re: Newbie: Simple select with 'IN ALL'
              RAMJANE
              Thnks User575882 to clear my doubt.
              You got the solutioon of your problem?
              • 19. Re: Newbie: Simple select with 'IN ALL'
                William Robertson
                More discussion and suggestions for relational division:
                groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/576ea61b1a93469b/74a1a03238b6d97b?lnk=gst

                @NightCabbage:
                > Oracle is like that.

                Perhaps, but relational division has historically always been tricky in the SQL language. I don't know whether other vendors make this type of query any easier.
                • 20. Re: Newbie: Simple select with 'IN ALL'
                  William Robertson
                  ...so using COLLECT and SUBMULTISET as in Maxim Demenko's post on c.d.o.m, we get this:
                  WITH project AS
                        ( select 'Carl' person, 3 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 )
                  --
                  SELECT person
                  FROM   project
                  GROUP  BY person
                  HAVING integer_tt(2,3,4) SUBMULTISET OF CAST(COLLECT(id_state) AS integer_tt) ;
                  "INTEGER_TT" is my nested table collection type, defined as
                  CREATE TYPE integer_tt AS TABLE OF INTEGER
                  • 21. Re: Newbie: Simple select with 'IN ALL'
                    569964
                    Volder
                    but would give wrong result in case duplicate records exist in the table!
                    hehe, yup! But I was trying to make it as small as possible.

                    You're dead right though, distinct id_state should be used instead of *

                    Also, I do wish Oracle had better set capabilities, etc.

                    There are always some things (not just sets) that seem really easy to do... until you actually try to implement them in SQL...
                    • 22. Re: Newbie: Simple select with 'IN ALL'
                      578885
                      Wouldn't it be nice if we had a set operator
                      say 'INALL' which would do the job!!

                      Actually I was looking for an answer like this as you can see from the subject line!
                      • 23. Re: Newbie: Simple select with 'IN ALL'
                        William Robertson
                        > Also, I do wish Oracle had better set capabilities, etc.

                        As I'm sure you know, relational division is a notoriously tricky challenge in SQL and many people have said that it highlights the weakness of SQL itself (although opinion is divided on whether it is a very common requirement or a rare and exotic one). I'd certainly be interested if Oracle added some new set operator to help with this but I doubt that it will ever be straightforward.

                        What else is missing, out of interest?
                        • 24. Re: Newbie: Simple select with 'IN ALL'
                          Vadim Tropashko-Oracle
                          This is rather innovative technique! However, the queries with explicit set operations are much more intuitive

                          select person from t where id_state = 2
                          intersect
                          select person from t where id_state = 3
                          intersect
                          select person from t where id_state = 4

                          which returns all the persons who's set of id_states contains {2,3,4}, and

                          select person from t where id_state = 2
                          intersect
                          select person from t where id_state = 3
                          intersect
                          select person from t where id_state = 4
                          minus
                          select person from t where id_state not in (2,3,4)

                          which returns all the persons who's set of id_states is exactly {2,3,4}.
                          • 25. Re: Newbie: Simple select with 'IN ALL'
                            Vadim Tropashko-Oracle
                            Pretending that we cast votes here, I would prefer extending the IN operator to handle subqueries for both operands, e.g.
                            select distinct Name from ApplicantSkills i
                            where (select Language from JobRequirements ii 
                                       where ii.Name = i.Name) 
                               IN 
                                      (select Language from ApplicantSkills)
                            where IN operator is interpreted as a "subset of"

                            Without an explicit subset relation available we have to express it as emptiness of the difference between the two sets. Formally
                            A ⊆ B
                            is equivalent to
                            A \ B = ∅
                            Applied to our case it allows us to transform our rough first attempt to a legitimate SQL query
                             
                            select distinct Name from ApplicantSkills i
                            where not exists ( 
                                select Language from ApplicantSkills
                                minus
                                select Language from JobRequirements ii 
                                where ii.Name = i.Name
                            )
                            So if the the IN or SUBSET OF operator would rewrite to the above "not exists" predicate, then the feature is minuscule.

                            Message was edited by:
                            Vadim Tropashko
                            • 26. Re: Newbie: Simple select with 'IN ALL'
                              William Robertson
                              That would make sense.

                              Or, perhaps SUBSET OF would be easier to read (not that the inventors of MODEL and partition outer joins seem to care about syntax being easy to read).
                              • 27. Re: Newbie: Simple select with 'IN ALL'
                                45736
                                Aketi's solution and COLLECT - SUBMULTISET seems to be two general solutions.

                                However, between these two solutions which will bring more "strain" on the database? From previous discussion on COLLECT in this forum it was shown that COLLECT generates more recursive calls. Does more recursive call means more "strain" on the database?

                                Thanks,

                                Raman
                                • 28. Re: Newbie: Simple select with 'IN ALL'
                                  45736
                                  My apologies...three solutions...

                                  the third one is:

                                  SQL> ed
                                  Wrote file afiedt.buf

                                  1 select person from project6030
                                  2 where id_state in (2, 3, 4)
                                  3 group by person
                                  4* having count(distinct id_state) = 3
                                  SQL> /

                                  Raman
                                  • 29. Re: Newbie: Simple select with 'IN ALL'
                                    William Robertson
                                    So in our people/states example with an explicitly specified set of states, that becomes:
                                    WITH projects AS
                                          ( select 'Carl' person, 3 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 )
                                       , people AS
                                          ( SELECT DISTINCT person FROM projects )
                                    --
                                    SELECT person
                                    FROM   people p
                                    WHERE  NOT EXISTS
                                           ( SELECT column_value FROM TABLE(INTEGER_TT(2,3,4))
                                             MINUS
                                             SELECT id_state FROM projects WHERE person = p.person );
                                    Nice.