Forum Stats

  • 3,734,036 Users
  • 2,246,863 Discussions
  • 7,857,005 Comments

Discussions

Newbie: Simple select with 'IN ALL'

578885
578885 Member Posts: 93
edited September 2007 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..
«1

Comments

  • 465815
    465815 Member Posts: 544
    edited August 2007
    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 August 2007
    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 September 2007
    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 August 2007
    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 August 2007
    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 September 2007
    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 August 2007
    i didnt see u posted there.

    Message was edited by:
    Hariharan M K
  • RAMJANE
    RAMJANE Member Posts: 317
    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.
  • 569964
    569964 Member Posts: 278
    edited August 2007
    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.
  • RAMJANE
    RAMJANE Member Posts: 317
    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.
  • 569964
    569964 Member Posts: 278
    This is probably the shortest query though:
    select person from project
    where id_state in (2, 3, 4)
    group by person
    having count(*) = 3
  • RAMJANE
    RAMJANE Member Posts: 317
    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.
  • 572471
    572471 Member Posts: 984 Green Ribbon
    This is probably the shortest query though:
    but would give wrong result in case duplicate records exist in the table!
  • RAMJANE
    RAMJANE Member Posts: 317
    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.
  • 578885
    578885 Member Posts: 93
    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!!
  • RAMJANE
    RAMJANE Member Posts: 317
    Thnks User575882 to clear my doubt.
    You got the solutioon of your problem?
  • William Robertson
    William Robertson Member Posts: 9,560 Bronze Crown
    edited August 2007
    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.
  • William Robertson
    William Robertson Member Posts: 9,560 Bronze Crown
    edited September 2007
    ...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
  • 569964
    569964 Member Posts: 278
    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...
  • 578885
    578885 Member Posts: 93
    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!
  • William Robertson
    William Robertson Member Posts: 9,560 Bronze Crown
    edited August 2007
    > 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?
  • Vadim Tropashko-Oracle
    Vadim Tropashko-Oracle Posts: 1,223 Employee
    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}.
  • Vadim Tropashko-Oracle
    Vadim Tropashko-Oracle Posts: 1,223 Employee
    edited August 2007
    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
This discussion has been closed.