<> not working

i have two table

fact
item_no  code
10001     ABC
10001    BCD
10002    BCD
10002    EFG



STAGE
item_no  code
10001     ABC
10001    BCD
10002    FGH
10002    EFG

I want result like

FCT
10002    BCD

SO i am runing below query which is not giving me desire result


select a.item_no
from fact a,stage b
WHERE
a.ITEM_NO = b.ITEM_NO
and a.CODE <> bCODE
sam2929Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave FordSoftware Developer / Database AdministratorCommented:
It looks like you want an exception join. Give me a minute, and I'll whip you up an example.

HTH,
DaveSlash
Dave FordSoftware Developer / Database AdministratorCommented:
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

Open in new window


HTH,
DaveSlash
momi_sabagCommented:
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
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Learn More!
tliottaCommented:
When this row is read from fact:
10001     ABC

Open in new window

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

Open in new window

It will satisfy this condition:
WHERE
a.ITEM_NO = b.ITEM_NO
and a.CODE <> bCODE

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
sam2929Author Commented:
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
momi_sabagCommented:
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
sam2929Author Commented:
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
sam2929Author Commented:
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
momi_sabagCommented:
my mistake
try

select t1.*
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MurpheyApplication ConsultantCommented:
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
lmduffyCommented:
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 )

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.