[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

<> not working

Posted on 2012-03-30
11
Medium Priority
?
329 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:sam2929
  • 3
  • 3
  • 2
  • +3
11 Comments
 
LVL 18

Expert Comment

by:Dave Ford
ID: 37787458
It looks like you want an exception join. Give me a minute, and I'll whip you up an example.

HTH,
DaveSlash
0
 
LVL 18

Expert Comment

by:Dave Ford
ID: 37787500
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 37788992
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 27

Expert Comment

by:tliotta
ID: 37789010
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
 

Author Comment

by:sam2929
ID: 37790976
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 37791053
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
 

Author Comment

by:sam2929
ID: 37792924
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
 

Author Comment

by:sam2929
ID: 37793146
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
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 total points
ID: 37794726
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
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 37805038
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
 

Expert Comment

by:lmduffy
ID: 37824099
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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Integration Management Part 2
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses
Course of the Month19 days, 8 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question