DISQUS

yas · 1 year ago

In this case it works. In some other cases it might not. Suppose products have some properties like color. What if I want the suppliers supplying all the red parts?

elai · 1 year ago

How about these two sets of queries for division in the case of having all red parts:

select distinct sid
from catalog c, parts p
where c.pid = p.pid and color = 'Red'
group by sid
having count(distinct c.pid) =
(select count(distinct pid)
from parts
where color = 'Red');

select distinct sid
from catalog c1
where not exists (
(select pid
from parts
where color = 'Red')
minus
(slect pid
from catalog c2
where c1.sid = c2.sid));

Ric · 1 year ago

I think this different approach should also work:
select sid, count(*) total
from catalog
group by sid having count(*) =
(select count(*) from parts);

laurentschneider · 1 year ago

what about this :

select sid from catalog
group by sid
having collect(pid) = (select collect(pid) from parts);


well, it is maybe slower

laurentschneider · 1 year ago

well, this does not cover when supplier have more pieces than parts. So maybe this :

create or replace type parts_t as object (pid number);
/
create or replace type parts_tt as table of parts_t;
/
select sid from
(select sid,cast(collect(parts_t(pid)) as parts_tt) x from catalog group by sid)
where
((select cast(collect(parts_t(pid))as parts_tt) from parts)) submultiset x;


yas · 1 year ago

Laurent, thanks for the great solution. I did not even know something like submultiset was available.

For the ones interested: submultiset does not exist in 9.2, it comes with 10G R1. It is used to find out if a nested table is a subset of another nested table. Documentation about it is here

Thanks again Laurent.

laurentschneider · 1 year ago

unfortunately it is quite underused, and it is not working as I expect it to do :

SQL> select sid
2 from catalog
3 group by sid
4 having
5 (select cast(collect(parts_t(pid))as parts_tt) from parts)
6 submultiset
7* cast(collect(parts_t(pid)) as parts_tt)
select sid
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression


the ORA-979 makes no sense. I will open a SR and it may be fixed in Oracle 15

yas · 1 year ago

Laurent, it works in 10.2.0.2. I did not try it on 10G R1.

SQL> r
1 select sid from
2 (select sid,cast(collect(parts_t(pid)) as parts_tt) x from catalog group by sid)
3 where
4* ((select cast(collect(parts_t(pid))as parts_tt) from parts)) submultiset x

SID
----------
1

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

yas · 1 year ago

Sorry, you are using a different sql in your last post. That does not work in 10.2 either.

elai · 1 year ago

How about these two sets of queries for division:

select distinct sid
from catalog c, parts p
where c.pid = p.pid
group by sid
having count(distinct c.pid) =
(select count(distinct pid)
from parts);

select distinct sid
from catalog c1
where not exists (
(select pid
from parts)
minus
(slect pid
from catalog c2
where c1.sid = c2.sid));

yas · 1 year ago

elai, yours also works.

elai · 1 year ago

Yas,

Thanks for your postings and comments regarding Relational Algebra: division in sql!

elai

Aparna · 1 year ago

The minus is a very costly affair, and your sql takes 10 times more time to execute than the otherquery you've posted and the one that I used as below:

select sid from (select sid, count(sid) count
from (select distinct sid, pid from catalog) c,
parts p where c.pid = p.pid group by sid) s
where s.count = (select count(distinct pid) from parts)

buy wow gold · 1 year ago

There’s also this alternative approach which is usually more efficient:

SELECT name
FROM Student
WHERE SID IN ( SELECT SID
FROM Register
GROUP BY SID
HAVING COUNT(*) = (SELECT COUNT(*) FROM Course)
)

hypnosis.course · 9 months ago

There’s also this alternative approach which is usually more efficient:

SELECT name
FROM Student
WHERE SID IN ( SELECT SID
FROM Register
GROUP BY SID
HAVING COUNT(*) = (SELECT COUNT(*) FROM Course)

rent limousine · 3 months ago

The post is so interesting and easy to understand. Relational algebra and its step by step solution has made the sql unproblematic and simple. Thank you for sharing.