Solved

<> not working

Posted on 2012-03-30
11
302 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:daveslash
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:daveslash
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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

820 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