| query [message #11127] |
Mon, 08 March 2004 13:37  |
Sril
Messages: 13
Registered: November 2002
|
Junior Member |
|
|
Hi,
I have 2 tables supplier & parts.
Supplier
SUPPLIERCODE PARTNO
------------ ----------
1 34
2 12
3 34
4 12
2 45
5 45
2 67
1 45
2 23
4 45
2 34
SUPPLIERCODE PARTNO
------------ ----------
2 56
6 12
2 78
Parts
PARTNO NAME
---------- --------------------
12 pen
23 pencil
34 eraser
45 scale
56 compass
67 sharpener
78 pencil box
The query is to retrieve the supplier who supplied all the parts.In my case, supplier 2 has supplied all parts. Is it possible to write it without using count function?
Thanks in advance.
Sril
|
|
|
|
| Re: query [message #11129 is a reply to message #11127] |
Mon, 08 March 2004 15:13  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Using COUNT would be more efficient, but here's one way to do it without count:
sql>select distinct suppliercode
2 from supplier
3 where suppliercode not in
4 (select suppliercode
5 from (select sp.suppliercode, s.suppliercode match
6 from (select distinct
7 s.suppliercode, p.partno
8 from supplier s, parts p) sp,
9 supplier s
10 where s.suppliercode (+)= sp.suppliercode
11 and s.partno (+)= sp.partno)
12 where match is null);
SUPPLIERCODE
------------
2
1 row selected. |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
