Select entregando resultados duplicados o triplicados

[Highlander]

Buenas estoy trabajando con WampServer tengo dos tablas:

Español y Registro

La consulta:

Código PHP:

  "SELECT registro.id, registro.apellido, registro.nombre,  registro. zip 
FROM registro, $nacionalidad 
WHERE registro.apellido = $nacionalidad.apellido 
AND $nacionalidad.apellido LIKE '$abc%'";  
   

($abc tiene la inicial que se esta buscando y $nacionalidad = español en esta consulta)

Cuando uso esta consulta en SQL puro me entrega 3800 resultados, pero cuando lo hago desde PHP me entrega 4800 aprox, al revisar manualmente cuales eran los resultados repetidos me di cuenta que ocurria cuando el apellido estaba guardado en la tabla Español varias veces de esta manera:

Perez, Pérez, Pèrez

Como puedo lograr que no me mustre los resultados duplicados cuando hago la consulta desde PHP sin tener que modificar la tabla Español?

Saludos y gracias.

[GatorV]

Trasladado a Base de Datos

[jurena]

Prueba esto
"SELECT DISTINCT registro.id, registro.apellido, registro.nombre, registro. zip
FROM registro, $nacionalidad
WHERE registro.apellido = $nacionalidad.apellido
AND $nacionalidad.apellido LIKE '$abc%'";

[quimfv]

Iniciado por Highlander

Cuando uso esta consulta en SQL puro me entrega 3800 resultados, pero cuando lo hago desde PHP me entrega 4800 aprox, al revisar manualmente cuales eran los resultados repetidos me di cuenta que ocurria cuando el apellido estaba guardado en la tabla Español varias veces de esta manera:

Perez, Pérez, Pèrez

Como puedo lograr que no me mustre los resultados duplicados cuando hago la consulta desde PHP sin tener que modificar la tabla Español?

Saludos y gracias.

Creo que tienes dos problemas:

Primero:

Perez, Pérez, Pèrez para la base de datos son apellidos completamente diferentes. No veo claro porque te da resultados distintos des de SQL y PHP, revisa la definición del conjunto de caracteres que estes usando en la bbdd (charset y collation) y en php. Lo normal seria que tanto de una forma como de la otra Perez, Pérez, Pèrez sean tres apellidos distintos.

Segundo:

Si por lo que sea te interesa que no se tengan en cuenta los acentos debes buscar una función que los elimine en el momento de hacer la comparación.

Quim

[jurena]

En cuanto al problema, estoy de acuerdo con Quimfv, pero no sé cuál es tu objetivo.
1) Si quieres que te sigan encontrando los Pérez, Pèrez, Perez, pero que no los cuente como dos cuando aparezca en dos, deberías utilizar el DISTINCT que te propuse al prinicipio, pues imaginemos que el registro.id nº 15 contiene Pérez, al hacer la consulta sería coincidente Pérez, Pèrez, Perez, puesto que la colación la tienes en ambas tablas es probablemente en _ci case insensitive. La búsqueda te carga tres veces el id nº 15, una por cada id de la tabla español coincidente, pero el distinct te deja solo uno para la cuenta. Para que la búsqueda elimine los acentos, no hacen falta funciones, sino la colación _ci en la tabla, por ejemplo UTF8_spanisch_ci. El charset seguirá siendo el mismo, utf8, pero al comparar los datos tendrá o no en cuenta los acentos, mayúsculas y minúsculas,según utilices las colaciones terminadas en _ci o por el contrario una colación que busque los acentos como UTF8_bin, por referirme al charset UTF-8.

2) Y, como te he dicho, si lo que quieres es que encuentre sólo lo que quieres, tal cual, te bastará con utilizar la colación utf8_bin en esta tabla.

[Highlander]

Voy por partes:

Agrege el DISTINCT a la consulta, desde PHP me muestra 3200 resultados y desde MySQL sigue mostrando los 3800, aparentemente el distinct no cambio nada en la consulta MySQL, revisando manualmente los resultados de PHP se ven bien, pero me queda la duda que paso con esos 600 resultados de diferencia entre PHP y MySQL.

Respecto a cambiar la colacion a utf8:bin en la tabla luego de hacer la consulta con DISTINCT no cambio en nada los resultados.

Segundo la tabla REGISTRO fue importada y acabo de descubrir que tiene duplicidad, me explico si hago una consulta buscando todas las personas con apellido inicial P encuentra:

ID, Nombre, Apellido

2, Pedro, Perez
9, Pedro Perez

Es decir la misma persona pero con distintos ID, estoy buscando ahora la manera de eliminar esos registros duplicados, basicamente una condicion donde si el nombre y apellido son iguales pero el ID distinto que elimine, pero no se me ocurre como podria hacerlo.

[jurena]

Imagino que tus tablas son MyISAM.

Las repeticiones parecen estar en la tabla Registro. Pues bien, como el id será siempre diferente, te bastará crear una tabla distinta con los resultados de eliminar los repetidos. Prueba con esto:

CREATE TABLE Resgistro2 AS SELECT * FROM Registro GROUP BY CONCAT(Nombre,Apellido) ORDER BY min(id)

Esto crea una tabla llamada Registro2 con los registros agrupados por la concatenación nombreapellido, y por tanto evita esas repeticiones. Conserva el id de la primera vez que lo incluiste. Mantiene todo lo demás. Hasta aquí no hay peligro.
Luego, tras revisar esa tabla Registro2 y ver que todo está bien con respecto a la tabla Registro, deberás eliminar la otra tabla, la vieja, Registro, y cambiar el nombre de la nueva, Registro2 a Registro.


Este procedimiento encierra un peligro, la eliminación de id de la tabla Registro, y por tanto, la posibilidad de que si de esos id dependen algún FK en otra tabla, estés causando un daño en la relación entre tablas. Comprueba que eso no ocurrirá, es decir, que el id de la tabla Resgistro no está guardado como FK en ninguna otra tabla.
Este sería el procedimiento si las tablas son MyIsam. Imagino que las InnoDB te impedirían hacer esos cambios si hubiera una relación establecida.

EDITO: es lógico que si dejas el distinct te salga el mismo resultado que si cambias a la colación utf8_bin en los dos campos, porque sólo encontrará uno para cada uno. De todas maneras, deberás vigilar la colación de las dos campos que se comparan, porque si en la primera has escrito también el apellido de manera diversa: Pérez, Pèrez, Perez y también en la segunda, en el caso de que la primera tenga colación utf8_bin entenderá cada apellido como uno diferente, al igual que ocurrirá en la segunda; si tú quieres que los de la primera tabla sean entendidos como iguales y también los de la segunda deberás usar colación _ci en ambas tablas y luego poner distinct en la selección de ese primer campo, pues si son tres iguales, aunque los encuentre en 9 lugares (cada uno lo encontraría en los otros 3), el distinct del primero, ya que son los tres, los nueve ahora, iguales, te dejaría sólo tres. El resultado sería el mismo. Eso creo. Estudia estas situaciones haciendo pruebas.

[Highlander]

Gracias pero mis tablas son InnoDB, pero ya voy ver como lo hago.

[Highlander]

Volviendo al tema, ya descubri realmente la razon de los registros duplicados, simplemente porque en realidad estan duplicados hasta cierto punto:

si hago select * from registro where nombre ='bertolo' and apellido = 'Antonino Wohnkultur'

Obtengo 8 registros con diferencias minimas por ejemplo: sale la misma persona registrada con una profesion o tambien sin profesion o puede salir registrada con un telefono y otra vez sale con dos, pero siempre se refiere a la misma persona, he llegado a encontrar 8 registros de la misma persona.

Ademas de esto esta el problema que tienen ID incremental por lo que usar SELECT DISTINCT no me sirve o al menos no se como.

Estuve probando eliminando primero el ID (luego de eliminar la duplicidad volver a agregarlo) luego hacer el SELECT DISTINCT y de 8 registros duplicados bajaba a 2 (esto en el caso especifico consulta por un nombre y apellido como lo hice arriba) pero no se como hacerlo de manera general digamos para toda la tabla, ni como eliminar lo que no me muestra cuando uso el DISTINCT.

[jurena]

Lo primero es que veas en qué tabla de las tuyas puedes utilizar esto. Yo he pensado que querías eliminar las repeticiones de donde tienes los teléfonos y profesiones.

Ojo, no pruebes esto directamente en tu tabla; crea otra y adapta los nombres para que no afecte a la tuya, de la que previamente debes haber guardado una copia; luego prueba esto sobre la tabla duplicada o creada al efecto para probarlo .

1) Debes vigilar que los id de esa tabla no sean utilizados en otra tabla (es decir que sean PK y aparezcan como FK, porque si eso es así deberás sacar con esta consulta los datos de los id por nombre y apellidos, imprimirlos en papel, y luego, al final de todo el proceso, en la otra tabla relacionada hacer los cambios pertinentes de los id borrados por el que queda de cada cliente.

Select nombre, apellido, group_concat (cast(id as char)) as identificadores, group_concat(teléfono) as teléfonos, group_concat(profesión) as profesiones from registro group by concat(nombre,apellido) having count(*) > 1 order by apellido, nombre

2) Esta consulta de actualización (peligro, cambia datos: pruébala con un duplicado de tu tabla antes) te rellena los campos de teléfono y profesión de todos los que tienen el mismo apellido y nombre. Ojo, porque yo he creído que sí puedes dejar en blanco el campo de teléfono y el de profesión, es decir, que no admite nulo. Si admite nulo tendrás que cambiar la sintaxis para que los encuentre...
UPDATE registro r1, registro r2 SET r1.teléfono = if(r1.teléfono LIKE '', r2.teléfono, r1.teléfono), r1.profesión =IF (r1.profesión LIKE '', r2.profesión, r1.profesión) where concat(r1.apellido,r1.nombre) LIKE concat(r2.apellido,r2.nombre) AND r1.id != r2.id

Pueden escaparse nombres compuestos del tipo de José y José Luis, apellidos dobles frente al simple, teléfonos dobles, etc. pero eso lo podrás ver luego con tu tabla original bien guardada...

3) Una vez comprobado que no hay problemas de relaciones, que están los campos completos, haces esto
CREATE TABLE registro2 as SELECT * FROM Registro GROUP BY CONCAT(Nombre,Apellido) order by min(id)

Creas una tabla que luego será la tabla. Probablemente perderás alguna pequeña cosa, pero ya tendrás resuelto parte del problema.
Luego tendrás que hacer un índice único de los dos campos, nombre y apellido para evitar que te suceda esto otra vez.

Puedes hacer otras cosas con programación, pero eso te requeriría conocimientos de PHP o algún otro programa.

[Highlander]

Gracias por tu respuesta, el problema de duplicidad en este caso es por ej:

Hago el select y encuentro:

nombre ! apellido ! telefono_1 ! profesion ! ciudad ! direccion ! email

pedro ! perez ! 20202020 ! ejecutivo ! madrid ! santiago 23 ! [email protected]
pedro ! perez ! 20202020 !! madrid ! santiago 23 ! [email protected]
pedro ! perez ! 20202020 ! ejecutivo ! madrid ! santiago 23 ! *[email protected]

Ademas cada uno tiene un ID incremental pero este lo puedo eliminar temporalmente.

Son muchos mas atributos que estos donde existen pequeñas diferencias pero es obvio que si tienen el mismo nombre, apellido, direccion y telefono se trata de la misma persona, el cliente (quien me proporciono la base de datos) me pidio que eliminara esta duplicacion basicamente deberia quedar 1 solo registro de la persona. Donde la prioridad es nombre, apellido, telefono, direccion, email, los demas campos pueden ser incluso eliminados.

Estuve probando el tipo de consulta que me indicaste claro respaldando antes la base de datos pero no logro llevarla a cabo, no se si por que la cantidad de registros es muy grande pero ni en 45min se ejecuta la consulta.

Se me habia olvidado pero la base de datos esta con mucha basura por ejemplos hay campos que vacios que tienen un espacio como dato en vez de null, la tabla no esta relacionada por PK o FK a ninguna otra, la tabla admite nulls en todos los campos menos el ID.

[jurena]

Highlander,
Lo primero que tienes que decidir es en qué medida te importa perder datos; porque si no te importa demasiado perder un teléfono o una dirección en el primer paso, te bastaría usar la última consulta que te puse. Luego ya tendrías que completar datos con tu otra tabla.

Haz esto:
CREATE TABLE registro2 as SELECT * FROM Registro GROUP BY CONCAT(Nombre,Apellido) order by max(id)
He pensado que el último incluido tendrá la información más actualizada.

Espero que esto no tarde mucho en hacerlo y se cuelgue. Pruébalo. No causará daño, puesto que sólo te crea una segunda tabla...

Y luego me dices.

[Highlander]

Gracias Jurena lo probe recien pero se colgo, voy ver si lo puedo separar la consulta de alguna manera.

Respecto a perder datos, nombre, apellido, telefono, direccion, ciudad, email son de los mas importantes, de por si ya estoy eliminando 8 atributos que no liste anteriormente por que son irrevelantes.

[jurena]

Me dices

Iniciado por Highlander

Respecto a perder datos, nombre, apellido, telefono, direccion, ciudad, email son de los mas importantes, de por si ya estoy eliminando 8 atributos que no liste anteriormente por que son irrevelantes.

Creo que no me he explicado bien. Lo que ocurre es que se copia una tabla exactamente igual que la que tienes, pero si, por ejemplo, tienes ese nombre apellido en 4 ocasiones, y a una le falta el teléfono, a otra le falta la dirección, a otra el email y a una los otros tres, al crear la tabla utilizando la agrupación nombre apellido y ordenando por el max(id) para que te deje el último de ellos, coge el último que incluiste solamente, y si a ese último es precisamente al que le faltan los tres datos, pues de momento habrás perdido esos tres datos, en este primer filtrado.
En cuanto al hecho de que la consulta se cuelga en el servidor o en casa con PHPMyAdmin, es lógico que así sea por los límites de tiempo con que te permite trabajar el primero y con el que trabaja el segundo. Yo te recomendaría hacerlo directamente en consola (no tengo experiencia, pero creo que ahí no tendrás ese problema) y si no puedes hacerlo en consola, que tampoco lo hagas en el servidor directamente sino en localhost y utilizando una API, como por ej. PHP para lanzar esa consulta y darle a PHP todo el tiempo del mundo, más allá de 30 segundos, para hacer su trabajo. En resumen: si lo haces desde consola, no creo que tengas problema o si trabajas con PHP en localhost. Y si estás desesperado, podrías exportar esa tabla y enviármela para que yo intentara hacerte la conversión en localhost y te enviara el resultado, la nueva tabla que podrías recuperar y cargar . Pero entiendo que el problema será el nivel de confidencialidad de los datos que manejas...
No sé si habrá otras opciones. Tal vez mejorarías la velocidad si indexaras los campos de los que hacemos el concat, nombre y apellido, y si además, eliminaras la última parte lo del max(id), con lo que te dejaría la primera aparición de ese nombre apellido. Tendrías que probarlo.
Ten paciencia: lo resolveremos
Y si estamos equivocados, alguien del foro o algún moderador nos dirá el mejor modo de hacerlo.
Suerte

[Highlander]

Resportandome, la siguiente consulta parece esta funcionando bastante bien:

CREATE TABLE c as SELECT * FROM Registro WHERE apellido LIKE 'c%' GROUP BY CONCAT(Nombre,Apellido) order by max(id)

La separe por inicial y voy creando una tabla por cada inicial, luego las voy a unir todas.

Con la tabla que contenia duplicidad al listar todas las personas con apellido inicial A encontraba 2300, con la tabla nueva bajo a 2000 lo cual parece estar bastante bien, ahora voy terminarlo completamente y comparar la cantidad fina lde registros con la que tenia antes.

Al parecer va funcionar, me resportare con el resultado mas tarde.

Saludos

[Highlander]

Finalmente logre ejecutar tu consulta Jurena, desde consola y demoro 58 minutos, la modifique un poco agregandole al concat el teleofono y dirección.

Todo parece ok por el momento, mañana pretendo dedicarme a revisarla manualmente.

Saludos y gracias.

[jurena]

Highlander.
Ojo a lo del concat teléfono y dirección, pues no sé si te resultará, pues si lo has aplicado a la consulta CREATE TABLE, volverás a tener duplicados. Me explico: si tienes dos Juan Pérez, uno con dirección y otro sin ella, si no aplicaste la consulta de los IF antes, volverás a tener dos Juan Pérez, y la verdad, no habrás hecho mucho, pues que tengan los cuatro datos iguales, nombre,apellido, teléfono y dirección, serán muy pocos, y será de esos pocos de los que sólo te quedará uno. Y si le aplicaste la consulta de actualización antes, será inútil, pues bastará con nombre y apellidos, pues todos los que tienen nombre y apellidos tendrán los mismos teléfono y dirección; y en ese caso, ¿para qué ralentizar más la consulta añadiendo otros dos campos al concat?
En realidad no sé cuál es la consulta que se ha demorado 58 minutos, pero si es la segunda, esa en que tratábamos de completar datos que faltaban de una y otra, es lógico, pues eran muchos tus datos y mucho el recorrido y comprobación mediante IF, pero seguro que te habrá hecho perder menos tiempo que si hubieras tenido que comprobar uno a uno los registros e ir cambiando y corrigiendo. Puedes haber perdido direcciones al sobreescribir o teléfonos al sobreescribir otras, pero nunca habrías borrado una existente.
Si es la última, la de creación de una nueva tabla, te tocará ahora revisar los datos e ir completándolos manualmente. Se podría hacer una consulta entre las dos tablas para ir rellenando huecos, pero ya no sé lo que tú piensas al respecto.
Lo que tendrás que hacer a partir de ahora es cuando no quieras que este tipo de cosas ocurran es controlar bien lo de el charset y sobre todo en tu caso la colación, y poner para evitar repeticiones índices UNIQUE a los campos nombre y apellido.

Me alegro de que esté resultando algo...

[Highlander]

Claro al menos eso imagine, antes de eso si comprobe que los registros duplicados siempre tenian el mismo telefono y dirección, la mayoria de los campos que diferian eran otros como el email, profesion.

La razon por la que le agrege mas campos al concat fue por que no estaba seguro que iba pasar si habian personas con el mismo nombre y apellido, supongo que me iba mantener una sola y perderia las demas.

Me gustaria convertir esta tabla a una base de datos donde por ejemplo la ciudad seria otra tabla que estaria relacionada a la tabla persona, pero como me toco trabajar con esta tabla ya hecha y modificarla lo menos posible no puedo.

[jurena]

Iniciado por Highlander

Me gustaria convertir esta tabla a una base de datos donde por ejemplo la ciudad seria otra tabla que estaria relacionada a la tabla persona, pero como me toco trabajar con esta tabla ya hecha y modificarla lo menos posible no puedo.

Yo no te aconsejaría que mantuvieras dos tablas relacionadas para usuario y ciudad. Piensa que un usuario no puede habitar en dos ciudades al mismo tiempo. Lo mejor es tener un campo para ciudad dentro de la tabla usuario; y, si tienes dos direcciones, haz dos campos dentro de la tabla usuario, dirección1 y dirección2, o dirección privada, dirección de trabajo.
Muy distinto sería si esas ciudades son los destinos de un trabajo de tipo comercial con visitas a distintas ciudades y lugares: en ese caso, yo sí haría una tercera tabla.

Suerte

[Highlander]

Me refiero a que tendria las tablas:

Ciudad
ID:
Nombre:

Persona:
ID:
Nombre:
Telefono:
ETC

La tabla persona tendria el ID de la tabla ciudad como FK, la relación seria que una persona habita una y solo una ciudad, la ciudad puede estar habitada por una o mas personas.

En todo caso esto ya es otro tema xD

Ahora estoy tratando de ordenar alfabeticamente la base de datos segun los apellidos, para lo cual hice:

ALTER TABLE registro ORDER BY apellido

Para mi perplejidad luego de 6 horas aun no terminaba asi que cerre el MySQL, no puedo creer que se demore tanto.

[jurena]

La orden es para mostrar los datos ordenados es

SELECT * FROM registro ORDER BY APELLIDO

En cuanto a lo de la ciudad, estoy en desacuerdo contigo. Eso es como desperdiciar las relaciones. Para qué necesitas dos tablas relacionadas cuando la consulta la harás sobre el usuario, es decir, sobre el id del usuario. Deja la ciudad en esa tabla de USUARIO y no te compliques cuando no es necesario: es mi consejo.

[Highlander]

Gracias en todo caso no planeo ni puedo hacer modificaciónes a la base de datos, solo hago lo que me piden.

En esto momento necesito mostrar los resultados de las consultas por orden alfabetico segun apellido.

Código:

"SELECT registro.id, registro.apellido, registro.nombre,  registro. zip 
FROM registro, $nacionalidad 
WHERE registro.apellido = $nacionalidad.apellido 
AND $nacionalidad.apellido LIKE '$abc%'" ORDER BY registro.apellido;

Pero de una consulta de 35secs subio a mas de 2min, por ahi lei que era mejor ordenar la base de datos de la manera que deseo mostrarla.

Saludos.

[jurena]

Tienes razón, aunque imagino que tendrás que hacerlo en las dos tablas que quieres relacionar. Yo, que desconocía lo que dices, hubiera probado a indexar los dos campos primero y a ponerlos en relación mediante inner join para ver lo que ocurre.
SELECT registro.id, registro.apellido, registro.nombre, registro. zip
FROM registro
INNER JOIN $nacionalidad ON registro.apellido = $nacionalidad.apellido
WHERE $nacionalidad.apellido LIKE '$abc%' ORDER BY registro.apellido;
pero, la verdad, no sé qué decirte al respecto.

[Highlander]

Ya estaba usando indices y ni con el inner join logro un incremento considerable, realmente lenta la consulta y me consume una cantidad grande es espacio en el disco duro mientras la realiza.

Sigo probando.

[jurena]

Vayamos a la raíz del problema. Creo que las relaciones no están bien hechas, es decir, de qué sirve tener un id (Pk numérico) en la tabla registro si no tiene un FK también numérico en la tabla $nacionalidad. Las relaciones deben establecerse con campos numéricos indexados (el PK lo está por naturaleza) y en las InnoDB no sé si lo estará también por naturaleza el FK. Si el id de registro o de $nacionalidad apareciera en la otra tabla, imagino que todo iría mucho más deprisa. De todas formas, seguro que alguien te echa una mano. Lo que no sé es si los índices te estarán funcionando...
Haz un explain a la consulta y comprueba los datos...

[Highlander]

Correcto la verdad que el ID solo lo usan para operaciones como eliminaciones.

Al probar con EXPLAIN me aparece algo que ya me habia percatado, solo me usa el indice de apellido de la tabla nacionalidad, de la tabla registro apellido no me esta usando el indice.

La verdad que esto va lento para todo, si quiero hacer cualquier modificación por ejemplo agregar el indice a apellido de registro 30min. Asi avanzo muy lento.

Agradezco tu atención Jurena de verdad, ya me has ayudado bastante.

[Highlander]

Código:

SELECT registro.id, registro.apellido, registro.nombre, registro.maiden, registro.calle, registro.numero, registro. zip, registro.ciudad, registro.telefono_1, registro.email
FROM registro
INNER JOIN $nacionalidad 
ON registro.apellido = $nacionalidad.apellido
WHERE $nacionalidad.apellido LIKE '$abc%'
ORDER BY registro.apellido, registro.nombre"

Borre los ID, Indices, los agrege nuevamente a ambas tablas.

y!!!!!

Esta de lujo 30 sec por consulta en comparar una tabla de 3.900.000 contra una de 10.000 ordenado por apellido.

MISSION COMPLETE.

Gracias a todos los involucrados en especial a Jurena.