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.

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.

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

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.

572471

This is probably the shortest query though:

but would give wrong result in case duplicate records exist in the table!

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.

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!!

RAMJANE

Thnks User575882 to clear my doubt.
You got the solutioon of your problem?

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.

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