Advanced search and mysql query

Question

Advanced search and mysql query

Navigation

#1 by (2 votes)
#2 by (0 votes)
#3 by (-1 votes)

3

Good morning, I have a problem when it comes to making a query, since it does not return the desired result.

The conditions for the consultations are marked by the user with a form similar to this one:

And this is the scheme of the tables:

and finally, the query I'm trying to use:

  SELECT DISTINCT m.Nombre_IUPAC,m.Nombre_Tradicional, r.Ruta, m.Imagen_Molecula, m.Codigo_Producto, m.idMolecula
FROM molecula as m 
join ruta as r
ON r.idRuta=m.Ruta_Imagen_Molecula
join molecula_has_organismo_productor as mo
on m.idMolecula=mo.Molecula_idMolecula
join organismo_productor as op
on op.idOrganismo_Productor=mo.Organismo_Productor_idOrganismo_Productor
left join carbono as c
on c.Molecula_idMolecula=m.idMolecula
    left join tipo_carbono as tc
    on tc.idTipo_Carbono=c.Tipo_Carbono_idTipo_Carbono
join actividad_biologica_has_molecula as abm
on abm.molecula_idMolecula=m.idMolecula
    join actividad_biologica as ab
    on ab.idActividad_Biologica=abm.Actividad_Biologica_idActividad_Biologica
        where m.Nombre_Tradicional like ''
        or m.Nombre_IUPAC like ''
        or m.Codigo_Producto like ''
        or op.Nombre like ''
        or m.Peso_Molecular BETWEEN '' AND ''
        or tc.Carbono = ''
        or c.Desplazamiento BETWEEN '' AND ''
        or ab.Nombre in ('Ipsum','Flamatoria')
            group by m.idMolecula
            having count(1)=2

The problem with all this consultation is that there are records that do not come out when I want to filter the options of biological activity, but they appear if I remove the relationship that I have with the producer organization. I will be frank, I will not handle the join with so many tables, and the problem will surely be there.

mysql sql phpmyadmin

asked by Ald 17.05.2017 в 11:42

source

3 answers

0

I do not try to prove anything because of the difficulty of the tables, but I also think that your problems are in the joins.

Normally the join that you are interested in doing is INNER JOIN Which are the fields that coincide in the two tables. I see some left join, that if you are not clear about what they are, this is probably the problem (JOIN = INNER JOIN, the 'inner becomes clarifying mode')

I recommend you take a look at this diagram of Venn diagrams that I think clarifies enough that data return the different types of JOINS.

link

answered by 17.05.2017 в 12:34

-1

Good morning, I think I understood what you're looking for, try adding a DISTINCT to the COUNT filter, to make sure that all the IN options match.

 SELECT DISTINCT m.Nombre_IUPAC,m.Nombre_Tradicional, m.idMolecula, op.Nombre as OrgProductor, tc.Carbono as Carbonom, ab.Nombre as ActividadBiologica
FROM molecula as m 
left join ruta as r ON r.idRuta=m.Ruta_Imagen_Molecula
left join molecula_has_organismo_productor as mo on m.idMolecula=mo.Molecula_idMolecula
left join organismo_productor as op on op.idOrganismo_Productor=mo.Organismo_Productor_idOrganismo_Productor
left join carbono as c on c.Molecula_idMolecula=m.idMolecula
left join tipo_carbono as tc on tc.idTipo_Carbono=c.Tipo_Carbono_idTipo_Carbono
left join actividad_biologica_has_molecula as abm on abm.molecula_idMolecula = m.idMolecula
left join actividad_biologica as ab on abm.Actividad_Biologica_idActividad_Biologica = ab.idActividad_Biologica
WHERE ab.Nombre IN ('Ipsum', 'Flamatoria')
GROUP BY m.idMolecula
HAVING COUNT(DISTINCT ab.Nombre) = 2

answered by 17.05.2017 в 13:28

Obtain a customized map by parts Read HTML from a WebBrowser

score 2 · Accepted Answer

Good morning, I think I understood what you're looking for, try adding a DISTINCT to the COUNT filter, to make sure that all the IN options match.

SELECT DISTINCT m.Nombre_IUPAC,m.Nombre_Tradicional, m.idMolecula, op.Nombre as OrgProductor, tc.Carbono as Carbonom, ab.Nombre as ActividadBiologica
FROM molecula as m 
left join ruta as r ON r.idRuta=m.Ruta_Imagen_Molecula
left join molecula_has_organismo_productor as mo on m.idMolecula=mo.Molecula_idMolecula
left join organismo_productor as op on op.idOrganismo_Productor=mo.Organismo_Productor_idOrganismo_Productor
left join carbono as c on c.Molecula_idMolecula=m.idMolecula
left join tipo_carbono as tc on tc.idTipo_Carbono=c.Tipo_Carbono_idTipo_Carbono
left join actividad_biologica_has_molecula as abm on abm.molecula_idMolecula = m.idMolecula
left join actividad_biologica as ab on abm.Actividad_Biologica_idActividad_Biologica = ab.idActividad_Biologica
WHERE ab.Nombre IN ('Ipsum', 'Flamatoria')
GROUP BY m.idMolecula
HAVING COUNT(DISTINCT ab.Nombre) = 2