Here is the problem:
-----------------------
create table A( a1 int, a2 int)
create table B( b1 int, b2 int)

insert A values(1,1)
insert A values(1,2)
insert A values(2,1)
insert A values(2,3)
insert A values(3,1)
insert A values(3,3)

insert B values(1,1)
insert B values(1,2)
insert B values(3,2)
insert B values(2,2)

What is the SQL query to find out the pairs existing in B, but not in A ?

I can solve this by using string functions, but my problem is I have to write a query that can execute on MSSQL, DB2 and ORACLE, and as you know they have differnt syntax for string functions.

Thanks in advance.

Regards,
s99shah.

pairs in B but not in A? left outer join, check for unmatched rows --
rudy
http://r937.com/

Hi,

TRY THIS

Select B.* from a,b
where (+) a1 = b1;

select b1, b2
from b
where not exists
(
select 1
from a
where a1 = b1 and a2 = b2
)

A third solution just for completeness - I guess rudy's join version is the most effective one (although Oracle will most probably generate the same execution plan for both queries)

It's a mysql code very clean. Thanks

you're right, it isn't

this is the ANSI SQL forum, not the Tsql forum

I completly understood what you said. I think shammat posted a mysql code.


Thanks anyway for the clarification.

um..... no

he posted ANSI SQL code

The query from satish_ct is not standard SQL. Everything else is/was.