Re: Rows not in a table

buurd_at_my-deja.com wrote:
>
> Hi!
> What is the best way of getting all the rows not in a table? I've got
> three tables. One table holds SOME of the possible combination of the
> other two. Now I have to find out witch combinations not in the other
> two. The only i got working is to take ALL posslble combination MINUS
> the one in the table but it seems like its slow to do the (almost) same
> query twice. Are there any better way to sovle this problem??

Roland,

  I'm not sure if this is what you want, but here goes. You can perform a Cartesian product between two tables by joining them without a join condition. This will give you all the possible join combinations. For instance, the obtain the Cartesian product between tables A & B:

   SELECT a.col1, a.col2, b.col1, b.col2    FROM a,b;

Then you can use the MINUS operator to find out what is not in table C:

   SELECT a.col1, a.col2, b.col1, b.col2    FROM a,b
   MINUS
   SELECT c.col1, c.col2, c.col3, c.col4    FROM c;

HTH,
Brian

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================