[sqlite] Re: Using a table as a negative filter.

[Igor Tandetnik]

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