How to satisfy two conditions at the same time

Question

How to satisfy two conditions at the same time

Navigation

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

2

Good afternoon, suppose I have a list of students with the list of subjects they passed, and I want to select the name of those who passed math and language, if I do:

SELECT nombreAlumno
FROM alumno
WHERE aprobo = matematica
AND
aprobo = lengua

This would not give any results since it approved would have to have those two values at the same time, and not each one separately, is there any query with which to do this in an easy and fast way? I mean, I would need something like the division of relational algebra, in which a data to be compared is given, with a list, in which the first data must be related to all the data in the list. Use mariadb.

mysql mariadb

asked by Ramiro 17.08.2016 в 20:55

source

2 answers

0

The answer of lfentanes is correct, but it may not be the best if you have many records, I leave you this other option:

select a.*
  from alumno a
       inner join aprobadas am on am.idAlumno = a.idAlumno and am.materia = 'MATEMATICAS'
       inner join aprobadas al on al.idAlumno = a.idAlumno and al.materia = 'LENGUA';

I do not know the optimizer capabilities of mysql or mariadb , but in some engines writing the query in this way will make the difference.

answered by 17.08.2016 в 22:21

Copy two objects in c ++ Twig, Render duplicated if use includes in the template

score 1 · Accepted Answer

I do not know the structure of your database, but I think what you need is the operator in and subqueries. I understand that you have a table of students, a table of subjects and a third table which is the relation of the first two, that is, your account with a foreign key to students and a foreign key to subjects. If this is the case, the query could be as follows:

SELECT * FROM alumno WHERE alumno.id IN (SELECT alumno_id FROM aprobadas WHERE  materia_id = 'MATEMATICAS') AND alumno.id IN (SELECT alumno_id FROM aprobadas WHERE materia_id = 'LENGUA')

With this query you would check if the student is on the list of those who passed math and in turn on the list of those who passed the language. It is worth mentioning that it is not necessary to use the IN operator and compare the student's id against the entire list of students who passed, since the student's id could be compared directly, if the data is in both tables:

SELECT * FROM alumno WHERE alumno.id = (SELECT alumno_id FROM aprobadas WHERE alumno_id = alumno.id AND materia_id = 'MATEMATICAS') AND alumno.id = (SELECT alumno_id FROM aprobadas WHERE alumno_id = alumno.id AND materia_id = 'LENGUA')

I hope I have correctly interpreted your doubt.