SQL Statement Does Not Throw the Data Needed

Navigation
0

I have a sentence that does not work for me in the right way,
Areas of medicine:

  • Clinical analyzes.
  • Pathological anatomy.
  • List item.
  • Anesthesia and resuscitation, among others.

What I need is for me to select the one.

  

Specialty ID (1) and specialty name (medicina) and   also tell me the number of areas (Análisis clínicos) ,   where the areas are activas and there are students.

It's Saying; an area may be active but without students (which is the part that I do not want to have).
CODE 

    SELECT 
      especialidad.especialidad_id,
      especialidad.especialidad_descripcion,
      COUNT(areas.*) AS cant,
    FROM
      especialidad
    INNER JOIN area ON (especialidad.especialidad_id = area_id)
    INNER JOIN estudiantes ON (area_id = estudiantes.estudiantes_id)
    WHERE
      area_estado_id = 1 ->(activa)<-
    GROUP BY
especialidad.especialidad_descripción.


This code shows me the specialties and tells me the areas that are active, whether or not students are enrolled in the area.

To have the clearest idea this is my mistake;
SPECIALTY | QUANTITY_of_REASES
MEDICINE | two
ENGINEERING IND. | 7
ENGINEERING INF. | 3
IMPORTANT: In this case MEDICINE has 2 areas the Análisis clínicos and Anatomía patológica. ; for Análisis clínicos there are 50 registrations and it is active but for Anatomía patológica. there is 0 but it is active; in that case then it should show me in quantity only 1.

STRUCTURE OF TABLES:

specialty

  • specialty_id
  • specialty_ description

area

  • area_id
  • area_description
  • area_estado
  • area_id_speciality
  • student_id

enrolled

  • enrolled_id
  • area_id
  • student_id
  • registration_date

student

  • student_id
  • student_name
  • student_surname

WHAT I REALLY NEED IS:
That show me all the specialties and tell me all the areas that are active and that there are students, it does not help me to show me the active areas but without students.

I would be very grateful if you could help me.

    
asked by JDavid 24.01.2017 в 15:26
source

1 answer

1

Try this 

   SELECT 
  especialidad.especialidad_id,
  especialidad.especialidad_descripcion,
  COUNT(areas.*) AS cant,
  COUNT(estudiantes.*) AS cant_estudiantes
FROM
  especialidad
INNER JOIN area ON (especialidad.especialidad_id = area_id)
INNER JOIN estudiantes ON (area_id = estudiantes.estudiantes_id)
WHERE
  area_estado_id = 1 ->(activa)<-
GROUP BY
especialidad.especialidad_descripción
HAVING cant_estudiantes > 0
    
answered by 24.01.2017 в 15:53
Python multiprocessing (Pool) error Create a new field within a table and enter the value of another but taking the value of a next row