SQL & PL/SQL

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.

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 =)

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> 

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

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

select person from project where id_state in(2,3,4) group by person having count(person)=3

thanks
hari

null

Hehe, 3rd time that solution has been posted ^^

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".

i didnt see u posted there.

Message was edited by:
Hariharan M K

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.

Problem is that this will select any people with exactly 3 rows, so someone with 1, 2, 6 would also be in.

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.

This is probably the shortest query though:

select person from project
where id_state in (2, 3, 4)
group by person
having count(*) = 3

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.

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

but would give wrong result in case duplicate records What do you mean by duplicate records in table?
any example.

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

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

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.

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

Volder 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...

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!

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

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}.

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

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).

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

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

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.

William i've tried this solution and it shows --

satyaki>
satyaki>CREATE TYPE integer_tt AS TABLE OF INTEGER;
  2  /

Type created.

satyaki>
satyaki>
satyaki>WITH project AS
  2        ( select 'Carl' person, 3 id_state from dual union all
  3          select 'Carl', 4 from dual union all
  4          select 'Joe',  3 from dual union all
  5          select 'Mark', 2 from dual union all
  6          select 'Mark', 3 from dual union all
  7          select 'Mark', 4 from dual union all
  8          select 'Paul', 1 from dual union all
  9          select 'Paul', 2 from dual union all
 10          select 'Sam',  2 from dual union all
 11          select 'Sam',  3 from dual )
 12  SELECT person
 13  FROM   project
 14  GROUP  BY person
 15  HAVING integer_tt(2,3,4) SUBMULTISET OF CAST(COLLECT(id_state) AS integer_tt) ;
HAVING integer_tt(2,3,4) SUBMULTISET OF CAST(COLLECT(id_state) AS integer_tt)
                         *
ERROR at line 15:
ORA-00920: invalid relational operator


satyaki>

Is it because i'm using Oracle 9i, so it is giving me an error?

Regards.

Satyaki De.

Yes, the multiset operators are a 10g feature.