<> not working

It looks like you want an exception join. Give me a minute, and I'll whip you up an example.

HTH,
DaveSlash

This works for me:

select f.item_no,             
       f.code                 
  from fact f           
  exception join stage s
    on s.item_no = f.item_no  
   and s.code = f.code

ITEM_NO   CODE
 10,002   BCD 

Select all Open in new window

HTH,
DaveSlash

select *
from fact
except
select *
from stage

or

select t1.*
from fact t1
 left outer join stage t2 on t1.item_no = t2.item_no and t1.code = t2.code
where t2.code is null and t2.item_no is null

When this row is read from fact:

10001     ABC

Select all Open in new window

...it will be compared against this row from stage:

10001    BCD

Select all Open in new window

It will satisfy this condition:

WHERE
a.ITEM_NO = b.ITEM_NO
and a.CODE <> bCODE

Select all Open in new window

Similarly, when the {10001    BCD} row is read from fact, it will satisfy the WHERE clause because stage has this row -- {10001     ABC}.

Whenever there are multiple rows with the same item_no in the tables, there will be rows that will satisfy the "<>" condition.

SQL doesn't line the rows up side by side for this comparison. It compares ALL of the rows every time.

That's why DaveSlash's EXCEPTION JOIN would be used.

Tom

its bit different what i explained earlier

fact
item_no  code
10001     ABC
10001    BCD
10002    BCD
10002    EFG
10002    FGH




STAGE
item_no  code
10002    FGH
10002    EFG

I want result like

FCT
10002    BCD

as stage item_no 10002 don't have that code so i want to select that item_no and the
code from fact which is not in stage

try

select t0.*
from stage t0 join fact t1 on t0.item_no = t1.item_no
 left outer join stage t2 on t1.item_no = t2.item_no and t1.code = t2.code
where t2.code is null and t2.item_no is null

should not inner join work
inner  join stage t2 on t1.item_no = t2.item_no and t1.code = t2.code
where t2.code is null and t2.item_no is null

try

select t0.*
from stage t0 join fact t1 on t0.item_no = t1.item_no
 left outer join stage t2 on t1.item_no = t2.item_no and t1.code = t2.code
where t2.code is null and t2.item_no is null

This don't work as it returns
10002    EFG
10002    FGH

Hi sam2929

Joining tables is an option but sometimes to slow,
try this;

SELECT a.item_no
  FROM  fact a
  WHERE (SELECT COUNT(*) WHERE b.code = a.code and b.item_no = a.item_no) = 0

Regards,
Murph

Try this:

Select *
From FACT a
Where Not Exists ( Select * 
                   From STAGE b
                   Where a.item_no = b.item_no
                     and a.code = b.code )

Select all Open in new window