Extracting Similar and Different data from 2 tables

[morfasie]

Hi,

I've got two tables, each containing similar and different data. I want to determine the data in the one that is not in the other and vica versa.

There seems to be differences in some of the Id numbers.

Table1:

firstname surname ID
beauty sithole 6104100333081
monna monareng 1401120000000

Table2:

Field5 Field10 Field11
6104100333081 B SITHOLE
1401120147086 M MONARENG

Field10 being the initials of the person - first letter of firstname

SELECT * 
FROM `Table1` 
LEFT JOIN `Table2` ON LEFT( `Table1`.id, 6 ) = LEFT( Table2.Field5, 6 ) 
WHERE LOWER( `Table1`.Surname ) = LOWER( `Table2`.Field11 ) 
AND LOWER( LEFT( `Table1`.`FirstName` , 1 ) ) = LOWER( LEFT( `Table2`.Field10, 1 ) )

There are approximately 2500 records in each table, the above query returns only 200
and I know for sure that it should be around 2000 that are similar.

SELECT `Table1`. * 
FROM `Table1` 
LEFT JOIN Table2 ON `Table1`.id = Table2.Field5
WHERE Table2.Field5 IS NULL 
ORDER BY `Table1`.id

This query returns approx 600 but a large amount of them are still incorrect

At the moment I'm stuck - Any help to fine tune this perfect
example of how differing data can make your life hell...

 

[nagornyi]

What does the following query return?

select *
from
  table1 t1
, table2 t2
where
    upper(substr(t1.firstname, 1, 1)) = t2.field10
and upper(t1.surname) = t2.Field11

 

[morfasie]

Hi nagornyi

Returns 3500 rows,
unfortuanetly there are still a lot of duplicate initial + surname. It returns for every match in table1 it returns one in table2.

Ex:

maria khumalo 1606170147089,6206145331085 M KHUMALO
maria khumalo 1606170147089,1606170147089 M KHUMALO
maria khumalo 1606170147089,6601016168089 M KHUMALO

I think it just needs a bit of fine tuning by including the
id. Slowly getting there

Thanks for your time.

Morfasie

 

[nagornyi]

What about this:

select *
from
  (select distinct firstname, surname from table1) t1
, (select distinct field10, field11 from table2) t2
where
    upper(substr(t1.firstname, 1, 1)) = t2.field10
and upper(t1.surname) = t2.Field11

 

[morfasie]

Hi,

The mysql version that I'm running doesn't support subqueries - NOT COOL - will have a look in Access, maybe I can link to the Odbc and try it through there.

Thanks

Morfasie