IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

To get Non matching row after comparing 2 tables


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 179
Location: India

PostPosted: Fri Jan 05, 2018 5:43 pm
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 Member


Joined: 29 Apr 2008
Posts: 939
Location: Maryland

PostPosted: Fri Jan 05, 2018 8:49 pm
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: 2455
Location: Hampshire, UK

PostPosted: Sat Jan 06, 2018 8:39 pm
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: 179
Location: India

PostPosted: Mon Jan 08, 2018 12:56 pm
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: 747
Location: Pennsylvania

PostPosted: Mon Jan 08, 2018 6:04 pm
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

Global Moderator


Joined: 21 Sep 2010
Posts: 2577
Location: NYC,USA

PostPosted: Mon Jan 08, 2018 8:30 pm
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 Member


Joined: 29 Apr 2008
Posts: 939
Location: Maryland

PostPosted: Mon Jan 08, 2018 11:54 pm
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: 179
Location: India

PostPosted: Thu Jan 11, 2018 7:01 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Column names in SYSIBM tables DB2 5
No new posts Matching and non matching records usi... DFSORT/ICETOOL 11
No new posts Need assistance formatting when joini... SYNCSORT 8
No new posts insert into session tables with UR DB2 0
No new posts Sync logic between VSAM files and DB2... COBOL Programming 9
Search our Forums:

Back to Top