View previous topic :: View next topic
|
Author |
Message |
vinu78
Active User
Joined: 02 Oct 2008 Posts: 179 Location: India
|
|
|
|
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 |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008 Posts: 2011 Location: USA
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
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 |
|
|
vinu78
Active User
Joined: 02 Oct 2008 Posts: 179 Location: India
|
|
|
|
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 |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006 Posts: 792 Location: Pennsylvania
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
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 |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008 Posts: 2011 Location: USA
|
|
|
|
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 |
|
|
Back to top |
|
|
vinu78
Active User
Joined: 02 Oct 2008 Posts: 179 Location: India
|
|
|
|
Dave,
Thanks for the suggestion. I will take that approach.
Thanks
Vinu |
|
Back to top |
|
|
|