Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
To get Non matching row after comparing 2 tables

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 154
Location: India

PostPosted: Fri Jan 05, 2018 5:43 pm    Post subject: To get Non matching row after comparing 2 tables
Reply with quote

Hi All,

I would like to extract the Non matching rows from Table A when the columns First Name and Middle Name and Last Name didn't match when compared with that in Table B.

Code:
Table A
ID   FIRST_NAME   MID_NAME   LAST_NAME    POLICY
10   RAM           HARI        KRISH      1010
10   RAJ           SEKHAR      MUKIM      1010
10   ABHISHEK      SHARMA      K          1010



Code:
TABLE B
ID   FST_NME      MD_NME      LST_NME      
10   RAM           HARI        KRISH
10   RAJ           SHARMA      MUKIM      
10   ABHISHEK      SHARMA      K   


Expected output since the Middle name in Table A didn't match with Table B

Code:
ID   FIRST_NAME   MID_NAME   LAST_NAME   POLICY
10   RAJ          SEKHAR      MUKIM      1010

The SQL query I used is
Code:

SELECT TABLEA.ID
  FROM TABLEA
    INNER JOIN TABLEB
    ON TABLEA.ID = TABLEB.ID
   AND TABLEA.POLICY = '1010'
   AND ((TABLEA.FIRST_NAME <> TABLEB.FST_NME) AND (TABLEA.MID_NAME <> TABLEB.MD_NME) AND (TABLEA.LAST_NAME <> TABLEB.LST_NME))
GROUP BY TABLEA.ID ;


The above query retrieved all the 3 rows from Table A instead of 1 row.
Can you please guide me with correct SQL condition.

Thanks
Vinu
Back to top
View user's profile Send private message

sergeyken

Active User


Joined: 29 Apr 2008
Posts: 303
Location: Maryland

PostPosted: Fri Jan 05, 2018 8:49 pm    Post subject:
Reply with quote

The inner join itself, in your notation, creates 9 rows

Code:
Table A                                            TABLE B
 ID   FIRST_NAME   MID_NAME   LAST_NAME    POLICY  ID   FST_NME       MD_NME      LST_NME       
 10   RAM           HARI        KRISH      1010    10   RAM           HARI        KRISH
 10   RAJ           SEKHAR      MUKIM      1010    10   RAM           HARI        KRISH
 10   ABHISHEK      SHARMA      K          1010    10   RAM           HARI        KRISH
 10   RAM           HARI        KRISH      1010    10   RAJ           SHARMA      MUKIM       
 10   RAJ           SEKHAR      MUKIM      1010    10   RAJ           SHARMA      MUKIM       
 10   ABHISHEK      SHARMA      K          1010    10   RAJ           SHARMA      MUKIM       
 10   RAM           HARI        KRISH      1010    10   ABHISHEK      SHARMA      K   
 10   RAJ           SEKHAR      MUKIM      1010    10   ABHISHEK      SHARMA      K   
 10   ABHISHEK      SHARMA      K          1010    10   ABHISHEK      SHARMA      K   

Next, according to your last AND expression, 3 rows satisfy your expression.
So, you get what you requested.

This is just a clue for you, not the ready-to-use solution.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2011
Location: UK

PostPosted: Sat Jan 06, 2018 8:39 pm    Post subject:
Reply with quote

Why make a mountain out of a molehill?
Code:
Select columns from tablea, tableb
where col1 of table a matches col1 of table b
and ...
and ... does not match ...;


Put SQLite on your PC (its free, no licence required) and pratice there.
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 154
Location: India

PostPosted: Mon Jan 08, 2018 12:56 pm    Post subject:
Reply with quote

Thanks for the reply.
The problem here is the below mentioned statement in the condition

Code:
AND ((TABLEA.FIRST_NAME <> TABLEB.FST_NME) AND (TABLEA.MID_NAME <> TABLEB.MD_NME) AND (TABLEA.LAST_NAME <> TABLEB.LST_NME))


I wanted all the 3 columns - First Name, Middle Name and Last name as a combination to not match. But here that 3 column values are not taken together with this <> condition. So all the 3 rows from Table A is coming as output instead of 1 row having RAJ SEKHAR MUKIM

Thanks
Vinu
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 710
Location: Pennsylvania

PostPosted: Mon Jan 08, 2018 6:04 pm    Post subject:
Reply with quote

Vinu,

I believe you have a misconception that is falsely leading to your 'expected results'

Do you think that your sql is simply comparing the 'first' row against the 'first' row, 'second' vs 'second', etc...?

First off, the concept of row order is purely in your head. DB2 has no such concept (unless you use an order by).

Secondly, your sql is checking every row on table a with every row on table b to resolve the matches.

The expected result set is correct.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1896
Location: NY,USA

PostPosted: Mon Jan 08, 2018 8:30 pm    Post subject:
Reply with quote

Since you are on Expert forum, it is expected you know EXISTS in DB2.
why don't you code something NOT EXISTS ( from table B based on names condition),

Also, basic thing is you are joining on TABLEA.ID = TABLEB.ID which is duplicate for all three rows, so it is acting as cartesian product, Group BY is useless here as that don't make decision as to which row to return.
Back to top
View user's profile Send private message
sergeyken

Active User


Joined: 29 Apr 2008
Posts: 303
Location: Maryland

PostPosted: Mon Jan 08, 2018 11:54 pm    Post subject:
Reply with quote

Rohit Umarjikar wrote:
Since you are on Expert forum, it is expected you know EXISTS in DB2.
why don't you code something NOT EXISTS ( from table B based on names condition),

Quote:
Location: India
Occupation: Sr Software Engineer
Mainframe Skills: COBOL,JCL,VSAM,DB2,IDMS,REXX,ISPF macros

36_8_9.gif icon_pray.gif
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 154
Location: India

PostPosted: Thu Jan 11, 2018 7:01 pm    Post subject:
Reply with quote

Dave,

Thanks for the suggestion. I will take that approach.

Thanks
Vinu
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Comparing two sequential files Mohan Kothakota COBOL Programming 14 Mon Apr 16, 2018 6:35 pm
No new posts Sum the fields in particular format f... sharana64 DFSORT/ICETOOL 9 Fri Jan 05, 2018 1:11 am
No new posts DB2 catalog tables Pavan kumar chauhan DB2 8 Thu Dec 21, 2017 6:32 pm
No new posts Comparing dates in different formats migusd SYNCSORT 4 Sat Nov 18, 2017 3:02 am
No new posts Matching and unmatched records!! Vignesh Sid SYNCSORT 3 Thu Nov 09, 2017 11:22 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us