[SOLUCIONADO] MySQL mostrar segun numero de coincidencias

[Marea2]

Hola a todos,

Dispongo de una base de datos MySQL en la que tengo profesores particulares registrados. Quiero hacer una consulta en la que introduciendo el municipio en el que vive el alumno y las asignaturas que quiere hacer, se muestren los 'id' de los profesores que viven en el municipio y que dan estas asignaturas.

La base de datos consta de los siguientes campos: 'id', 'municipio', 'asign1', 'asign2', 'asign3' y 'asign4'.

El problema surge en este punto:

En la consulta no solo busco los profesores que den exactamente las 4 asignaturas que quiere el alumno, sino que me gustaría que salieran los profesores que dan 3 de estas 4 asignaturas, y después 2 de estas 4 asignaturas, hasta llegar a los que dan 1 de estas 4 asignaturas. Esto es, que la consulta ordene las 'id' de los profesores según el numero de asignaturas coincidentes, empezando por los que coinciden 4 de 4, hasta los 1 de 4.

He intentado hacerlo usando combinaciones de AND y OR, pero no lo he logrado. He leído acerca de la función MATCH, pero no lo acabo de ver .

Os agradecería si pudierais indicarme como puedo hacer-lo.

Muchas gracias de antemano.

[leonardo_josue]

Hola Marea2:

Vayamos por partes... en primer lugar, ¿de cuantas tablas estamos hablando? es decir, por un lado hablas de alumnos, por otro lado hablas de profesores y por otro lado hablas de materias, pero no nos dices cómo tienes almacenada tu información, ni cuál es la estructura de tus tablas...

Dependiendo de esta información puede haber más de una posibilidad para lo que quieres hacer... postea algunos datos de ejemplo y dinos qué es lo que esperas de salida, así será mas posible que te podamos ayudar con tu problema

Saludos
Leo.

[Marea2]

Hola,

Toda la información esta almacenada en una misma tabla llamada 'usuarios'.

En esta tabla hay 6 campos: 'id', 'municipio', 'asign1', 'asign2', 'asign3', 'asign4'. Estos 6 campos contienen información de profesores particulares. 'Id' es un 'bigint' con auto_increment.

El campo 'municipio' es de tipo 'int', en el que se escribe el municipio en que vive el profesor con un numero (ej. Barcelona ->3 ).

Finalmente, en 'asign1', 'asign2', 'asign3', 'asign4' (varchar) se encuentran las asignaturas que el profesor enseña. Se escribe de forma abreviada (Ej. matemáticas -> mate ).

Por ejemplo, tenemos:


+---------+-------------+------------+-----------+----------+---------------+
| id | municipio | asign1 | asign2 | asign3 | asign4 |
|--------------------------------------------------------------------------------+
| 100 | 3 | mate | quim | biol | diss |
|----------------------------------------------------------------------------------
| 101 | 3 | quim | lles | diss | mate |
|---------------------------------------------------------------------------------
| 102 | 3 | mate | llca | lica | fisi |
|----------------------------------------------------------------------------------
| 103 | 3 | diss | llca | lica | fisi |
|----------------------------------------------------------------------------------
| 104 | 4 | quim | fisi | diss | biol |
|----------------------------------------------------------------------------------

Entonces, tal y como he explicado en la primera entrada, en la consulta no solo busco los profesores que den exactamente las 4 asignaturas que quiere el alumno, sino que me gustaría que salieran los profesores que dan 3 de estas 4 asignaturas, y después 2 de estas 4 asignaturas, hasta llegar a los que dan 1 de estas 4 asignaturas. Esto es, que la consulta ordene las 'id' de los profesores según el numero de asignaturas coincidentes, empezando por los que coinciden 4 de 4, hasta los 1 de 4.


Si en la consulta, quiero buscar un profesor según las asignaturas: mate, quim y biol; y en el municipio '3'.

El resultado seria:

+----------+
| id |
+----------+
| 100 | //coincide con las 3 asignature de la busqueda ( mate, quim y biol )
|-----------+
| 101 | //coincide con 2 asignaturas de la busqueda ( mate y quim)
|-----------+
| 102 | // coincide con 1 asignaturas de la busqueda ( mate )
|-----------+


El orden es de mas coincidencias a menos, hasta llegar a una sola coincidencia.
Espero que me haya echo entender.

Muchas gracias y si hay alguna duda ruego que me preguntéis.

[jlct]

Hola Marea2 lo que buscas lo puedes obtener con la siguiente consulta:

Código MySQL:

Select id from usuarios where municipio = 3  
and ((asign1='mate' or asign2='mate' or asign3='mate' or asign4='mate') and (asign1='quim' or asign2='quim' or asign3='quim' or asign4='quim') and  (asign1='biol' or asign2='biol' or asign3='biol' or asign4='biol'))
union
Select id from usuarios where municipio = 3  
and (((asign1='mate' or asign2='mate' or asign3='mate' or asign4='mate') and (asign1='quim' or asign2='quim' or asign3='quim' or asign4='quim')) or 
((asign1='mate' or asign2='mate' or asign3='mate' or asign4='mate') and (asign1='biol' or asign2='biol' or asign3='biol' or asign4='biol')) or 
((asign1='quim' or asign2='quim' or asign3='quim' or asign4='quim') and (asign1='biol' or asign2='biol' or asign3='biol' or asign4='biol'))) 
union
Select id from usuarios where municipio = 3  
and ((asign1='mate' or asign2='mate' or asign3='mate' or asign4='mate') or (asign1='quim' or asign2='quim' or asign3='quim' or asign4='quim') or (asign1='biol' or asign2='biol' or asign3='biol' or asign4='biol'))
 

aquí no solo se prueban las combinaciones que indicaste (mate,quim,biol), (mate,quim) y (mate), sino que mostrara cuando existan 3 coincidencias, 2 coincidencias y 1 coincidencia.

Espero te sirva, Saludos.

[leonardo_josue]

Hola Marea2:

En primer lugar, desde mi punto de vista tienes un mal modelo de tablas... si tienes una relación n a m, como es el caso, ya que n profesores pueden impartir m materias, el modelo correcto es tener tres tablas, una para los profesores, otra para las materias y otra para relacionar ambas tablas... tal como lo tienes, nada te impide que tengas algo como esto:

Código MySQL:

+------+-----------+--------+--------+--------+--------+
| id   | municipio | asign1 | asign2 | asign3 | asign4 |
+------+-----------+--------+--------+--------+--------+
|  100 |         3 | mate   | mate   | mate   | mate   |
+------+-----------+--------+--------+--------+--------+
 

es decir, un maestro que la misma materia en todas los campos... y qué pasa si un maestro imparte sólo 1 materia... ¿dejas los otros campos como NULL? o qué pasa si imparte más de cuatro materias... ¿lo truncas solo a cuatro? si haces cualquiera de esto es un algo terrible, si fueras estudiante de diseño de bases de datos estarías reprobad@ y si fueras emplead@ de una empresa, serías despedid@.

Pero bueno, ese es un tema que no tiene nada que ver con lo que preguntas... la consulta la puedes hacer de muchas formas... por ejemplos puedes hacer una suma condicional, algo asi:

Código MySQL:

mysql> SELECT * FROM tabla;
+------+-----------+--------+--------+--------+--------+
| id   | municipio | asign1 | asign2 | asign3 | asign4 |
+------+-----------+--------+--------+--------+--------+
|  100 |         3 | mate   | quim   | biol   | diss   |
|  101 |         3 | quim   | lles   | diss   | mate   |
|  102 |         3 | mate   | llca   | lica   | fisi   |
|  103 |         3 | diss   | llca   | lica   | fisi   |
|  104 |         4 | quim   | fisi   | diss   | biol   |
+------+-----------+--------+--------+--------+--------+
5 rows in set (0.00 sec)
 
mysql> SELECT
    -> id, SUM(IF(asign1 IN ('mate', 'quim', 'biol'), 1, 0) +
    ->         IF(asign2 IN ('mate', 'quim', 'biol'), 1, 0) +
    ->         IF(asign3 IN ('mate', 'quim', 'biol'), 1, 0) +
    ->         IF(asign4 IN ('mate', 'quim', 'biol'), 1, 0)
    ->        ) total
    -> FROM tabla
    -> WHERE municipio = 3
    -> GROUP BY id
    -> HAVING total > 0
    -> ORDER BY total DESC;
+------+-------+
| id   | total |
+------+-------+
|  100 |     3 |
|  101 |     2 |
|  102 |     1 |
+------+-------+
3 rows in set (0.00 sec)
 

es decir, verificas si cada uno de los campos donde almacenas las materias tiene alguna de las materias y los vas contando, (suma 1 si existe la materia y 0 si no). con el WHERE filtras sólo los profesores para el municipio que quieres y con el HAVING filtras aquellos profesores que no imparten ninguna de las materias.

Dale un vistazo y espero que te sirva.

@jlct: es terriblemente ineficiente el usar tantos OR's... en su defecto utiliza IN, tiene el mismo efecto, pero la consulta es mas simple.

Saludos
Leo.

[jlct]

Iniciado por leonardo_josue

@jlct: es terriblemente ineficiente el usar tantos OR's... en su defecto utiliza IN, tiene el mismo efecto, pero la consulta es mas simple.

Saludos
Leo.

Muchas gracias por la aclaratoria.

Saludos.

[Marea2]

Muchas gracias a los dos!

Jlct al final he usado el codigo que me has proporcionado. Muchas gracias.

[Marea2]

Una ultima pregunta:

En la salida de esta consulta, aparecen dos columnas ('id' y 'total'). He intentado recoger los datos de la consulta con PHP y con 'id' no tengo problemas pero con 'total' si que tengo, ya que no los recoge. En principio debería ser capaz de recogerlos tal y como esta montada la consulta no?

[gnzsoloyo]

Los nombres de las columnas del resultado de una query son tomados por los lenguaje de programación como nombres de las columnas de las tablas (en algunos casos), o bien de los indices de los arrays asociativos (en tu caso).
Si bajo ese nombre no lo está tomando bien, es porque la columna o no tiene alias, o el alinas es otro.
Verifica exactamente el alias de la columna y usa ese.