I still do not understand the exact meaning of the query you supplied,
but to make it more readable I would do something like this:
select c.sid
-- count all the red products by supplier
, count(0) cnt
from parts p
, catalog c
where p.pid = c.pid
and p.color = 'red'
group by c.sid
-- And limit it to those who have all red products
having count(0) =
(select count(0) cnt
from parts t
where t.color = 'red'
)
Best Regards, Ben de Boer
"Ramu" <haisubbu_at_fastmail.fm> wrote in message
news:1139292071.308861.151890_at_g43g2000cwa.googlegroups.com...
> I have a problem while understanding the query.....I want to explain it
> clearly.....
>
> My Tables are
>
> 1.suppliers
> 2.parts
> 3.catalog
>
> The tables and the records are looks like this
> ------------------------------------------------------------------
>
> suppliers
> -------------
> create table suppliers(
> sid number(9,0) primary key,
> sname varchar2(30),
> address varchar2(40)
> );
>
> Records in Suppliers Table
>
> 1,'Acme Widget Suppliers','1 Grub St., Potemkin Village, IL 61801'
> 2,'Big Red Tool and Die','4 My Way, Bermuda Shorts, OR 90305'
> 3,'Perfunctory Parts','99999 Short Pier, Terra Del Fuego, TX 41299'
> 4,'Alien Aircaft Inc.','2 Groom Lake, Rachel, NV 51902'
>
>
>
> parts
> -------
>
> Creating Table Parts
>
> create table parts(
> pid number(9,0) primary key,
> pname varchar2(40),
> color varchar2(15)
> );
>
> Records In Parts Table
>
> 1,'Left Handed Bacon Stretcher Cover','red'
> 2,'Smoke Shifter End','black'
> 3,'Acme Widget Washer','red'
> 4,'Acme Widget Washer','silver'
> 5,'I Brake for Crop Circles Sticker','translucent'
> 6,'Anti-Gravity Turbine Generator','cyan'
> 7,'Anti-Gravity Turbine Generator','magenta'
> 8,'Fire Hydrant Cap','red'
> 9,'7 Segment Display','green'
>
> catalog
> ----------
>
>
> create table catalog(
> sid number(9,0),
> pid number(9,0),
> cost number(10,2),
> primary key(sid,pid),
> foreign key(sid) references suppliers,
> foreign key(pid) references parts
> );
>
> Records In Catalogue Table
>
> 1,3,0.50
> 1,4,0.50
> 1,8,11.70
> 2,3,0.55
> 2,8,7.95
> 2,1,16.50
> 3,8,12.50
> 3,9,1.00
> 4,5,2.20
> 4,6,1247548.23
> 4,7,1247548.23
>
> The problem Is
> to find the SIDs of the suppliers who supply every RED Part
>
> The Query for this is
>
> select c.sid from catalog c where not exists(select p.pid from parts p
> where p.color ='red' and not exists(select c1.sid from catalog c1
> where c1.sid=c.sid and c1.pid=p.pid))
>
> The answer is SID-2
>
>
> Now i need a favor here in this question from you......
>
> 1.Please kindly tell me the result set of the third subquery
> 2.Please kindly tell me the result set of the second subquery where we
> placed the color='red' statement
> 3.Please tell me whether the second subquery where we placed
> c1.sid=c.sid and c1.pid=p.pid,fetches any records from the first sub
> query,and if so what are the records used in the second subquery for
> comparision....(c1.pid=p.pid)
> 4.Please explain me the query(if possible),mentioning how not exists
> worked, with a little paragraph..
>
>
> And i am also attaching the tables and records by a file....please
> kindly find it....
>
>
> And i am requesting you please help me....By explaining the query....I
> am very thankful to you
>
Received on Tue Feb 07 2006 - 08:14:47 CST
