Doubt when performing an SQL query for an advanced search engine

Question

Doubt when performing an SQL query for an advanced search engine

Navigation

#1 by (1 votes)

1

Good afternoon.

I am doing a website and a database for my practice, together with a colleague. Among the functions of the web, there is an advanced search engine, with several options.

My problem arises in making a concrete query between 3 tables, which are (summarized):

Table "molecule": Id, Name, Molecular weight, etc. (..)

Table "biological activity": Id, Name

Intermediate table: idMolecula, idActivity_Biological

A molecule can have several activities and vice versa. The problem appears when I want to know which molecules have activity 1 and activity 5, for example.

SELECT DISTINCT m.Nombrefrom molecula as m 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 ab.Nombre="Lorem" and ab.Nombre="Ipsum"

The problem is that I have manually checked that the molecule has both properties, but it does not show it to me. On the other hand, if I do it separately, there's no problem.

mysql sql

asked by Carlos Gargallo 16.05.2017 в 15:26

source

1 answer

Ruby on Rails - Simpe_Calendar. Visualize the weeks of two in two Help with c ++ program? [closed]

score 1 · Answer 1

I guess what you're looking for is this:

select m.Nombre
from molecula as m 
join actividad_biologica_has_molecula as abm 
    on abm.molecula_idMolecula=m.idMolecula 
where 
    abm.Actividad_Biologica_idActividad_Biologica in (1,5)
group by m.Nombre
having count(1) > 2

I understand that the join to biological_activity is not necessary for this query