Forum Stats

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

Discussions

Newbie: Simple select with 'IN ALL'

24

Comments

  • 569964
    569964 Member Posts: 278
    edited Aug 30, 2007 3:04AM
    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 Blue Ribbon
    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 Blue Ribbon
    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 Blue Ribbon
    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 Blue Ribbon
    Thnks User575882 to clear my doubt.
    You got the solutioon of your problem?
  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown
    edited Aug 30, 2007 7:12AM
    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,568 Bronze Crown
    edited Sep 1, 2007 3:48AM
    ...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
This discussion has been closed.