<> 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?
 
momi_sabagConnect With a Mentor Commented:
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
0
 
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
0
 
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
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
0
 
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
0
 
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
0
 
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
0
 
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
0
 
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
0
 
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
0
 
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

0
All Courses

From novice to tech pro — start learning today.