Thanks Igor!
This solves a big question for me :-)
Ran
On 3/29/06, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
>
> Ran <[EMAIL PROTECTED]> wrote:
> > For example:
> >
> > create temp table A(col1 varchar, col2 varchar, col3 varchar, col4
> > varchar);
> >
> > insert into A values('a', 'A', '1', 'n');
> > insert into A values('a', 'a', '2', 'e');
> > insert into A values('b', 'B', '3', 'n');
> > insert into A values('a', 'A', '4', 'n');
> > insert into A values('b', 'b', '5', 'e');
> > insert into A values('c', 'c', '6', 'n');
> >
> > create temp table B(col1 varchar, col2 varchar, col3 varchar);
> >
> > insert into B values('a', '1', 'a');
> > insert into B values('b', '2', 'b');
> > insert into B values('d', '3', 'd');
> > insert into B values('e', '3', 'e');
> >
> > So, in order to get all the rows of table A where col1 and col2 are
> > equal to any of the col1 and col3 of the rows of table B, one can
> > write:
> >
> > select A.col1, A.col2, A.col3, A.col4 from A, B where A.col1 =
> > B.col1 and A.col2 = B.col3;
> >
> > Which returns:
> >
> > a|a|2|e
> > b|b|5|e
> >
> > However, how could I get the other rows - so those rows in table A
> > which do NOT match to the rows of table B (using the same columns)?
>
> select * from A where not exists
> (select * from B where A.col1 = B.col1 and A.col2 = B.col3);
>
> or
>
> select A.* from A left join B on (A.col1 = B.col1 and A.col2 = B.col3)
> where B.col1 is null;
>
> Igor Tandetnik
>
