Good evening,
The problem is to identify the suppliers who supply exactly the same set of parts as a specified/chosen supplier. For instance, given the following rows:
SUPPLIERNAME PARTNAME QUOTE
-------------------------------- -------------------------------- ----------
NEW YANKEE WORKSHOP, INC. HAMMER 1.89
NEW YANKEE WORKSHOP, INC. NAIL .19
OLD YANKEE WORKSHOP, INC. HAMMER 2.09
TOOL TIME, INC. HAMMER 1.99
TOOL TIME, INC. NAIL .2
MORE TOOLS, INC. HAMMER 1.99
MORE TOOLS, INC. NAIL .2
MORE TOOLS, INC. PLYERS 3.2
HANDY TOOLS, INC. HAMMER 1.99
HANDY TOOLS, INC. PLYERS .2
NEW YANKEE and TOOL TIME supply the same set of parts. If NEW YANKEE is chosen then the query should return TOOL TIME and viceversa. Since those are the only two suppliers that supply exactly the same parts, choosing any other supplier should return the empty set.
I came up with the following solution:
select distinct suppliername
from quote
where suppliername not in
(
--
-- set of suppliers who supply at least one part not
-- supplied by the specified supplier
--
select suppliername
from quote
where partname not in (
select partname
from quote
where suppliername like '&&suppliername%'
)
union
--
-- set of suppliers who do not supply at least one
-- part supplied by the specified supplier
--
select suppliername
from supplier cross join
(
select partname
from quote
where suppliername like '&&suppliername%'
)
where (suppliername, partname) not in (
select suppliername,
partname
from quote
)
)
and suppliername not like '&&suppliername%'
;
which works but, feels a bit convoluted and complicated.
The question is: Is there a simpler way of obtaining the same result ?
Thank you very much for your help, (Note: I used "like" in the query to spare everyone - and myself - from having to type the entire supplier name)
John.
DDL follows: (never mind the use of varchar instead of varchar2)
/* ------------------------------------------------------------------ */
/* table supplier */
create table supplier
(
suppliername varchar(32) not null,
primary key(suppliername)
);
insert into supplier values
('NEW YANKEE WORKSHOP, INC.');
insert into supplier values
('OLD YANKEE WORKSHOP, INC.');
insert into supplier values
('TOOL TIME, INC.');
insert into supplier values
('MORE TOOLS, INC.');
insert into supplier values
('HANDY TOOLS, INC.');
/* ------------------------------------------------------------------ */
/* table quote */
create table quote
(
suppliername varchar(32) not null,
partname varchar(32) not null,
quote number(8,2) not null,
primary key(suppliername, partname),
foreign key(suppliername) references supplier
);
insert into quote values
('NEW YANKEE WORKSHOP, INC.', 'HAMMER', '1.89');
insert into quote values
('NEW YANKEE WORKSHOP, INC.', 'NAIL', '0.19');
insert into quote values
('OLD YANKEE WORKSHOP, INC.', 'HAMMER', '2.09');
insert into quote values
('TOOL TIME, INC.', 'HAMMER', '1.99');
insert into quote values
('TOOL TIME, INC.', 'NAIL', '0.20');
insert into quote values
('MORE TOOLS, INC.', 'HAMMER', '1.99');
insert into quote values
('MORE TOOLS, INC.', 'NAIL', '0.20');
insert into quote values
('MORE TOOLS, INC.', 'PLYERS', '3.20');
insert into quote values
('HANDY TOOLS, INC.', 'HAMMER', '1.99');
insert into quote values
('HANDY TOOLS, INC.', 'PLYERS', '0.20');
Edited by: 440bx - 11gR2 on Aug 3, 2010 7:19 PM - grammar correction, changed "to" to "than"