[SOLUCIONADO] Extraer registros con 2 campos dependientes

[burlock_spock]

Hola,
Tengo una duda que no consigo resolver. La planteo sin más:

Sobre una BBDD MySQL con muchos campos que guarda información sobre personas (nombre, telefono, idiomas hablados, nivel de cada idioma hablado...) quiero hacer una consulta que extraiga todos los registros que cumplan una serie de condiciones. Hasta aquí todo normal (SELECT * FROM `tabla` WHERE blablabla). La dificultad viene porque hay un par de campos interrelacionados. Me explico: ambos campos son varchar que en su interior guardan diferentes palabras separadas por un "|". El primer campo guarda idiomas (ej. spanish|english|german). El otro campo guarda el nivel existente para cada idioma (ej. C2|B1|B2).

Lo explico mejor con un ejemplo. La idea es que un usuario pueda buscar personas que tengan, por ejemplo, nivel C2 en aleman. La query más lógica para ello sería "SELECT * FROM `tabla` WHERE `idioma` LIKE '%german%' AND `nivel` LIKE '%C2%'". Nótese el LIKE porque en el campo se guarda más información. Pero claro, si yo tengo 2 registros en mi BBDD como los siguientes:

"Juan" "666666666" "spanish|english|german" "C2|B1|C2"
"Pedro" "677766666" "spanish|english|german" "C2|B1|B1"

Al hacer la búsqueda anterior me va a devolver los 2 registros, cuando solo debería devolverme el de Juan. Teniendo los datos guardados así, ¿se os ocurre alguna manera de extraer una query que solo devuelva el registro de Juan?

Si lo veis imposible y creéis que hay otra manera que no implique cambiar tooooodo lo que tengo picado, o cualquier otra idea o sugerencia, estaría encantado de escucharla.

He intentado ponerlo todo lo claro que se me ha ocurrido, sin extenderme una eternidad. En cualquier caso, si alguien tiene alguna pregunta para aclararse con lo que he dicho, que me diga sin problemas.

Gracias por adelantado.

[gnzsoloyo]

Tu problema es que has violado la Primera Forma Normal de una forma flagrante, y ahora te topas con problemas de manual, porque tu base de datos está mal diseñada a.
Tienes dos opciones.
La primera es parchar todas y cada una de las consultas que hagas, para intentar usar los datos mal formados que tienes. Y lo que tienes es mucha basura en las tablas, sin ofender.
La segunda es tomar el toro por las astas y corregir ese diseño y todo lo que hayas hecho, logrando una base eficiente, que permita consultas optimizadas, alimentando la aplicación con información útil y consistente.

¿Qué eliges?

[burlock_spock]

No ofendes gnzsoloyo,
Me metí a hacer esto para aprender. Y el caso es que le estuve dando vueltas a cómo configurar la tabla y no di con la manera de tener esa información, que es dinámica, de otro modo.

Por supuesto que quiero agarrar el toro por los cuernos!! Pero para eso necesito que alguien me guie al menos (trato de ser autodidacta, pero como ves, llega un punto en que necesito un cable). Por lo que, si se te ocurre algo robusto y escalable (poder meter tantos idiomas+niveles como se desee) siguiendo la 1FN, te estaría enormemente agradecido. Se acepta también alguna lectura que, al menos para este caso concreto, me diga cómo seguir.

Si es que igual es con que alguien me dé la idea para que yo en mi cabeza lo vea con claridad, que ahora estoy atascado, dándole vueltas, y no le veo la salida :(

[burlock_spock]

Por cierto, ¿podrías decirme a qué te refieres por basura en las tablas?

[gnzsoloyo]

Sobre lo último, me refiero a que tienes ambos multivaluados, cosa prohibida en el modelo relacional. Todo campo multivaluado genera inconsistencias y redundancia nociva, dos elementos que destruyen una base de datos.

Luego vemos lo otro. Ahora no estoy en casa.

[gnzsoloyo]

Bueno, volviendo al punto, cuando tienes N valores posibles para un campo o conjunto de campos, estos N valores nos indican que existe una relación 1:N entre la entidad base y otra que depende de ella. En tu caso lo que nos dice es que cada usuario o persona registrada en la tabla tiene como atributo idiomas que maneja, en cada uno de los cuales puede tener un único nivel de capacitacion.
Esto nos indica que hay al menos dos tablas (puede que más de dos) que faltan: idiomas_individuo, o algo así, donde se registra cada uno de los idiomas que la persona maneja con su respectiva categoría de conocimiento (B1, C2, etc.). Esa relación se define por medio de la FK que apunta a persona, y muy probablemente a otra tabla que nos indica la lista de idiomas registrados, cosa conveniente para evitar riesgos de inconsistencias de nombres si estos se ingresan manualmente.
A eso podría sumarse una de categorías o niveles, pero si la cantidad es inferior a 255, eso podría manejarse con un campo ENUM, sobre el que puedas buscar por nombre o indice. Es una decision de diseño.

Si tuviesemos esas dos tablas, tu consulta se reduciría a esto:

Código MySQL:

SELECT * 
FROM personas P 
  INNER JOIN idioma_persona IP ON P.idpersona = IP.idpersona
  INNER JOIN idioma I ON IP.id_idioma = I.id_idioma
WHERE InombreIdioma = 'german' 
  AND IP.nivel = 'C2';
 

Incluso, si quieres todos los que manejan alemán en más de un nivel, podrías hacer:

Código MySQL:

SELECT * 
FROM personas P 
  INNER JOIN idioma_persona IP ON P.idpersona = IP.idpersona
  INNER JOIN idioma I ON IP.id_idioma = I.id_idioma
WHERE InombreIdioma = 'german' 
  AND IP.nivel IN('C2', 'C1', 'B2');
 

con lo que no necesitas andar parseando el contenido del campo nivel, porque el valor contenido en cada registro de idioma en cada persona es único siempre.

Nota: LIKE es una cláusula que hay que manejar con cuidado porque tiende a generar resultados falsos en consultas cuando las cadenas de texto buscadas tienen menos de cinco caracteres.
Además, tampoco se lo debe usar con comodines en ambos lados porqu eeso genera FULL TABLE SCAN, lo que es la peor forma e consulta posible después del producto cartesiano.

Ver: Primera Forma Normal: Repeticion de grupos dentro de columnas

[burlock_spock]

Hola gnzsoloyo (y disculpa por no haber contestado antes, lios con el trabajo),

Sí que existe una tabla (bien formada) en la que se guardan los idiomas seleccionables. En cuanto a la otra tabla (o tablas) me dio miedo siquiera plantearme crearla porque pensé: "si a lo mejor un individuo habla entre 3-5 idiomas, y la tabla de individuos (o usuarios) creciese hasta los 1000, 2000, 100000..., la otra tabla de 'idiomas_individuo' que comentas crecería exponencialmente y a lo mejor llegaría a ser inmanejable" ¿Es este un pensamiento muy retrógrado o podría estar encaminado? ¿O a lo mejor tendría que crear una tabla "idiomas_individuo" por cada individuo (que yo creo que esto sería aún peor para la BBDD)?

Con la Clave Foránea (FK) es otra de las cosas con las que siempre me lío. No tengo problemas (o muchos) cuando la relación es entre 2 tablas, pero cuando hay más por medio... :( Entiendo que la Clave Primaria sería, por ejemplo, el Documento de Identidad del usuario, pero... ese DI ¿sería clave foranea del mismo campo en la tabla "idiomas_individuo", y este a su vez de tendría como Clave Foránea el nombre del idioma que es el nombre del idioma en la tabla "idiomas"? ¿O es justo al revés? :(

Como te he dicho, estoy empeñado en hacer bien las cosas, si no vaya manera de aprender. Me va a tocar hacerlas 20 veces, pero al final me lo grabo a fuego en las venas y por mis muelas que me lo aprendo :)

Mientras voy mirando y trasteando, espero tu respuesta (AKA siguiente lección).

Gracias.

[burlock_spock]

Buenas de nuevo,

Decidí rehacer la tabla afectada y ahora no tengo campos multivaluados. Para ello creé otras tablas en las que, mediante Foreign Keys (realmente no declaré FKs, solo relacioné los campos en la cláusula INNER JOIN), guardar estos campos multivaluados. El problema que esto me ha generado ahora es que, al hacer las búsquedas obtengo registros "duplicados". Lo explico con un ejemplo:

Mi tabla principal tiene los siguientes campos (indico solo unos cuantos):
ID, DNI, Nombre, Apellidos, Sexo, Teléfono, Localidad...
Como cada persona puede hablar uno o más idiomas cree una tabla que los almacenase de la siguiente manera:
ID, DNI, Idioma, Nivel.

Si Juanito, con DNI 11222333D habla un par de idiomas, tendrá 2 entradas en la tabla de idiomas, tal que así:

1, 11222333D, Español, LenguaMaterna
2, 11222333D, Inglés, B1

Decido realizar una consulta que extraiga toda la información de aquellos usuarios masculinos que vivan en Madrid. Mi deseo es poder tratar el registro generado para cada usuario, y mostrarlo por pantalla. El problema es que, con el INNER JOIN, para Juanito me ha generado 2 registros:
11222333D, Juanito, Gutierrez, Hombre, 600100400, Madrid, Español (idioma)
11222333D, Juanito, Gutierrez, Hombre, 600100400, Madrid, Inglés (idioma)
Obteniendo así información redundante.
Como MySQL entiende que no son registros duplicados, DISTINCT no funciona y, tras varias pruebas y bastante buscar, no he dado con la forma "sana" de remediarlo. Yo quiero que solo me devuelva 1 registro en el que aparezcan todos los datos, así como los 2 idiomas. Solo en 1 registro. ¿Es eso posible?

[gnzsoloyo]

Con GROUP_CONCAT, solo a los efectos de ser mostrado en la pagina web.

[burlock_spock]

Hola de nuevo a todos, que aunque solo me haya contestado gnzsoloyo (gracias, por cierto), imagino que habrá más gente al otro lado.

No entiendo muy bien a qué te refieres con "solo a los efectos de ser mostrado en la página web". Así que, mientras trataba de buscar info sobre GROUP_CONCAT he avanzado un poco más y he definido mejor el modelo de datos, agregando realmente PK y FK (antes solo "controlaba por código" la entrada y borrado de datos). Si quereis y se puede (que no sé si esto se consideraría como código) os pego aquí las consultas usadas para crear las tablas foráneas, por si ayuda el conocer las restricciones y demás parámetros. Creo que la BBDD está ahora un poquito mejor que antes (espero), y mediante el uso de GROUP_CONCAT he conseguido eliminar la duplicidad de registros, no obstante, una vez más, aparece un nuevo problema...

Antes de usar GROUP_CONCAT obtenía varios resultados para el mismo registro (como ya comenté). Ahora, solo obtengo un registro en el que aparecen datos duplicados y, lo peor, la información mezclada de todos los registros que realmente deberían aparecer. Copio, sin más, los ejemplos del ANTES y el AHORA.

ANTES:

03485121T - checked - Lola - Magañanán - 1980-05-12 - 0034-967331457 - Spanien - Albacete - Albacete - 666000111 - [email protected] - separated - pa_000001 - Argentina - spanish - mothertongue - Auxiliar de Enfermería - Instituto de Formación Profesional Iniesto, Albacete - 2001-06-13 - 2004-06-16 - Nurse - Hospital General de Albacete - Enfermera de Urgencias - 2006-01-09 - 2009-05-20 - Albacete - España - Realizaba cuidados intensivos, primeros auxilios, y todas aquellas tareas que me eran requeridas y por las que me daban órdenes -

03485121T - checked - Lola - Magañanán - 1980-05-12 - 0034-967331457 - Spanien - Albacete - Albacete - 666000111 - [email protected] - separated - pa_000001 - Argentina - english - B2 - Auxiliar de Enfermería - Instituto de Formación Profesional Iniesto, Albacete - 2001-06-13 - 2004-06-16 - Nurse - Hospital General de Albacete - Enfermera de Urgencias - 2006-01-09 - 2009-05-20 - Albacete - España - Realizaba cuidados intensivos, primeros auxilios, y todas aquellas tareas que me eran requeridas y por las que me daban órdenes -

03485121T - checked - Lola - Magañanán - 1980-05-12 - 0034-967331457 - Spanien - Albacete - Albacete - 666000111 - [email protected] - separated - pa_000001 - Argentina - german - B2 - Auxiliar de Enfermería - Instituto de Formación Profesional Iniesto, Albacete - 2001-06-13 - 2004-06-16 - Nurse - Hospital General de Albacete - Enfermera de Urgencias - 2006-01-09 - 2009-05-20 - Albacete - España - Realizaba cuidados intensivos, primeros auxilios, y todas aquellas tareas que me eran requeridas y por las que me daban órdenes -

03485121T - checked - Lola - Magañanán - 1980-05-12 - 0034-967331457 - Spanien - Albacete - Albacete - 666000111 - [email protected] - separated - pa_000001 - Argentina - spanish - mothertongue - Auxiliar de Enfermería - Instituto de Formación Profesional Iniesto, Albacete - 2001-06-13 - 2004-06-16 - Nursing Professional - Hospital General de Albacete - Enfermera de Urgencias - 2006-01-09 - 2009-05-20 - Albacete - España - Realizaba cuidados intensivos, primeros auxilios, y todas aquellas tareas que me eran requeridas y por las que me daban órdenes -

......

X0000013J - checked - Tata - Martino - 1970-05-13 - - España - Barcelona - Barcelona - 654684614 - [email protected] - married - pa_000005 - Argentina - spanish - mothertongue - Entrenador de Fútbol - Escuela de Futbolistas de Vélez - 1997-01-14 - 1999-09-22 - Building Architect - F.C.Barcelona - Entrenador del 1er equipo - 2013-07-01 - 2014-06-18 - Barcelona - España - Pues era el entrenador, el mister... -

X0000013J - checked - Tata - Martino - 1970-05-13 - - España - Barcelona - Barcelona - 654684614 - [email protected] - married - pa_000005 - Argentina - italian - C1 - Entrenador de Fútbol - Escuela de Futbolistas de Vélez - 1997-01-14 - 1999-09-22 - Building Architect - F.C.Barcelona - Entrenador del 1er equipo - 2013-07-01 - 2014-06-18 - Barcelona - España - Pues era el entrenador, el mister... -

AHORA:

03485121T - checked - Lola - Magañanán - 1980-05-12 - 0034-967331457 - Spanien - Albacete - Albacete - 666000111 - [email protected] - separated - pa_000001 - Argentina - spanish|english|german|italian - mothertongue|B2|C1 - Auxiliar de Enfermería|Master en Análisis Clínicos|Entrenador de Fútbol - Instituto de Formación Profesional Iniesto, Albacete|Centro de Estudios Cañete, Albacete|Escuela de Futbolistas de Vélez, Argentina - 2001-06-13|2004-05-31|1997-01-14 - 2004-06-16|2005-06-23|1999-09-22 - Nurse|Nursing Professional|Building Architect - Hospital General de Albacete|F.C.Barcelona - Enfermera de Urgencias|Entrenador del 1er equipo - 2006-01-09|2013-07-01 - 2009-05-20|2014-06-18 - Albacete|Barcelona - España - Realizaba cuidados intensivos, primeros auxilios, y todas aquellas tareas que me eran requeridas y por las que me daban órdenes|Pues era el entrenador, el mister... -

Como se puede ver, antes repetía todos los campos iguales, haciendo combinaciones de registros en los que cambiaba aquellos campos diferentes. Ahora solo muestra 1 registro en el que está mezclada la información de los 2 registros que debería mostrar (y encima faltaría toda la info del principio (tabla de la PK) correspondiente al 2º (y siguientes, en caso de haberlos) registros. Y encima ni siquiera pinta la otra nacionalidad (Española) del 1er registro.

Lo que yo quiero es muy sencillo de mostrar (lo escribo a mano):

Lo que yo quiero es muy sencillo de mostrar (lo escribo a mano):
03485121T - checked - Lola - Magañanán - 1980-05-12 - 0034-967331457 - Spanien - Albacete - Albacete - 666000111 - [email protected] - separated - pa_000001 - Argentina|Española - spanish|english|german - mothertongue|B2|B2 - Auxiliar de Enfermería|Master en Análisis Clínicos - Instituto de Formación Profesional Iniesto, Albacete|Centro de Estudios Cañete, Albacete - 2001-06-13|2004-05-31 - 2004-06-16|2005-06-23 - Nurse|Nursing Professional - Hospital General de Albacete - Enfermera de Urgencias - 2006-01-09 - 2009-05-20 - Albacete - España - Realizaba cuidados intensivos, primeros auxilios, y todas aquellas tareas que me eran requeridas y por las que me daban órdenes -

X0000013J - checked - Tata - Martino - 1970-05-13 - - España - Barcelona - Barcelona - 654684614 - [email protected] - married - pa_000005 - Argentina - spanish|italian - mothertongue|C1 - Entrenador de Fútbol - Escuela de Futbolistas de Vélez - 1997-01-14 - 1999-09-22 - Building Architect - F.C.Barcelona - Entrenador del 1er equipo - 2013-07-01 - 2014-06-18 - Barcelona - España - Pues era el entrenador, el mister... -

Es decir, lo que yo quiero son 2 registros, que a pesar del filtro puesto, devuelvan toda la información para ese campo, pero en 1 solo registro, no en N registros combinados entre sí esos campos difrerentes.

Espero haberme explicado bien, porque repasándolo, no parece tan claro como lo tengo en la cabeza.

[burlock_spock]

Buenas,
Tras orinar sangre, y muchas pruebas y error y buscar combinaciones, al final lo saqué. Muchas gracias gnzsolo, porque si no hubiera sido por todo lo que me has dicho, no hubiera conseguido enfilarlo, además de la lección para tener una BBDD un poquito mejor preparada.