Home » SQL & PL/SQL » SQL & PL/SQL » sql outer join help needed.
sql outer join help needed. [message #201366] Fri, 03 November 2006 13:55 Go to next message
Monica
Messages: 37
Registered: February 2000
Member
Can someone show me what I am doing wrong.
select kc.kitnum, ct.ctype as AllTypes, kc.ctype
from kit_components kc, componenttypes ct
where ct.ctype = kc.ctype(+)
order by kc.kitnum, ct.ctype

I want to find all the kitnum in kit_components that do not have a match to all componenttypes.

The data looks something like this:

kit_components
kitnum ctype
1 a
1 b
2 a
2 b
3 a
3 b
3 c

componenttypes
ctype
a
b
c

I would like the query to give the following:

kitnum ctype
1 c
2 c

Re: sql outer join help needed. [message #201367 is a reply to message #201366] Fri, 03 November 2006 14:01 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This could be one way to do it:
SELECT DISTINCT k.kitnum, c.ctype
FROM KIT_COMPONENTS k, COMPONENTTYPES c
WHERE  (k.kitnum, c.ctype) NOT IN (SELECT k1.kitnum, c1.ctype
                                   FROM KIT_COMPONENTS k1, COMPONENTTYPES c1
                                   WHERE k1.ctype = c1.ctype
                                   );


Here's another one:
SELECT k.kitnum, c.ctype
  FROM KIT_COMPONENTS k, COMPONENTTYPES c
MINUS
SELECT k.kitnum, c.ctype
  FROM KIT_COMPONENTS k, COMPONENTTYPES c
  WHERE k.ctype = c.ctype;


Although those queries *might* return what you need, for anything but really small tables both would be a disaster as they contain Cartesian products.

[Updated on: Fri, 03 November 2006 14:13]

Report message to a moderator

Previous Topic: Problem with Sequence Value after Export
Next Topic: bad binding error on after insert trigger!
Goto Forum:
  


Current Time: Sun Dec 04 08:18:17 CST 2016

Total time taken to generate the page: 0.09544 seconds