CODEWITHSUNDEEP
mysqlsql
user2212773
user2212773

Reputation: 128

Difference between 2 tables show some similarities

I'm usign two tables in my PHP application, I want all the rows that are in newLista but don't exist in oldLista, both have the same structure

id | vinculo | tipo_dni | numero_dni | apellido | nombre | fech_nac | sexo | numero_tit | ABM | convenio

I have already trying using RIGHT JOIN but it won't show any results:

SELECT * 
FROM newlista a 
RIGHT JOIN oldlista b ON a.numero_dni = b.numero_dni 
WHERE b.numero_dni IS NULL

The code that I'm usign is this:

SELECT * 
FROM newlista AS a 
WHERE NOT EXISTS ( 
    SELECT * 
    FROM oldlista AS b 
    WHERE a.numero_dni=b.numero_dni AND a.apellido = b.apellido 
) 
ORDER BY a.apellido ASC

But it keep showing me some persons that have the exact same numero_dni and same apellido

Upvotes: 0

Views: 59

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

This code should do what you want:

SELECT a.*
FROM newlista a
WHERE NOT EXISTS (SELECT 1
                  FROM oldlista a2
                  WHERE a2.numero_dni = a.numero_dni AND a2.apellido = a.apellido
                 )
ORDER BY a.apellido ASC;

This is basically your query.

If you are seeing duplicates, it is because the values look the same but are not. Some issues are easy to fix. Others are harder. Here are some examples:

  • Leading/trailing spaces. This can be fixed using trim().
  • Confused characters, as one and lower-case L or an em-dash versus an en-dash. Squinting or changing the font might help.
  • Hidden characters, such as a backspace that might not show up.
  • Characters from an alternative character set that are or look the same.

Upvotes: 0

alexherm
alexherm

Reputation: 1362

In that case:

select * 
from newlista 
where numero_dni not in (select numero_dni 
                         from oldlista)
    and apellido not in (select apellido
                         from oldlista)

Upvotes: 0

stickabee
stickabee

Reputation: 145

select * from newlista
minus
select * from oldlista

Upvotes: 1

Related Questions