Get row value inside a subquery I need to count days between dates. I have this query, that gets the date when an issue is resolved. The subquery selects the date when a issue is created.

The problem is that the subquery the dates of all opened issues instead of getting the one that has the same INCIDENCIA.ID_INCIDENCIA:

SELECT 
  INCIDENCIA.ID_INCIDENCIA AS Incidencia,
  INCIDENCIA.DESCRIPCION,
  SITUACION.FECHA,
  SITUACION.FECHA - Inicio.Fecha AS Dias
FROM
  INCIDENCIA,
  SITUACION,
  (SELECT SITUACION.FECHA AS Fecha 
    FROM SITUACION, INCIDENCIA
    WHERE SITUACION.ID_ESTADO = '1' AND INCIDENCIA.ID_INCIDENCIA = SITUACION.ID_INCIDENCIA) Inicio
WHERE
  SITUACION.ID_ESTADO = '3' AND
  INCIDENCIA.ID_INCIDENCIA = SITUACION.ID_INCIDENCIA
ORDER BY
  INCIDENCIA.ID_INCIDENCIA ASC


The expected result is:


╔═══╦══════════╦═══════════════════════╦═════╗
║ 1 ║ 13/10/16 ║ Averia en router      ║  30 ║
║ 2 ║ 16/11/16 ║ Fallo red             ║  28 ║
║ 3 ║ 22/10/16 ║ Solicitud de sala     ║  37 ║
║ 4 ║ 19/10/16 ║ Averia ordenador      ║   0 ║
║ 5 ║ 29/10/16 ║ Averia sin determinar ║   1 ║
║ 6 ║ 10/01/17 ║ Averia conexion       ║ 136 ║
╚═══╩══════════╩═══════════════════════╩═════╝


But instead I got:


╔═══╦═══════════════════════╦══════════╦═════╗
║ 1 ║ Averia en router      ║ 13/10/16 ║  30 ║
║ 1 ║ Averia en router      ║ 13/10/16 ║  -6 ║
║ 1 ║ Averia en router      ║ 13/10/16 ║  28 ║
║ 1 ║ Averia en router      ║ 13/10/16 ║  -6 ║
║ 1 ║ Averia en router      ║ 13/10/16 ║ -15 ║
║ 1 ║ Averia en router      ║ 13/10/16 ║  47 ║
║ 2 ║ Fallo red             ║ 16/11/16 ║  64 ║
║ 2 ║ Fallo red             ║ 16/11/16 ║  28 ║
║ 2 ║ Fallo red             ║ 16/11/16 ║  62 ║
║ 2 ║ Fallo red             ║ 16/11/16 ║  28 ║
║ 2 ║ Fallo red             ║ 16/11/16 ║  19 ║
║ 2 ║ Fallo red             ║ 16/11/16 ║  81 ║
║ 3 ║ Solicitud de sala     ║ 22/10/16 ║  39 ║
║ 3 ║ Solicitud de sala     ║ 22/10/16 ║   3 ║
║ 3 ║ Solicitud de sala     ║ 22/10/16 ║  37 ║
║ 3 ║ Solicitud de sala     ║ 22/10/16 ║   3 ║
║ 3 ║ Solicitud de sala     ║ 22/10/16 ║  -6 ║
║ 3 ║ Solicitud de sala     ║ 22/10/16 ║  56 ║
║ 4 ║ Averia ordenador      ║ 19/10/16 ║  36 ║
║ 4 ║ Averia ordenador      ║ 19/10/16 ║   0 ║
║ 4 ║ Averia ordenador      ║ 19/10/16 ║  34 ║
║ 4 ║ Averia ordenador      ║ 19/10/16 ║   0 ║
║ 4 ║ Averia ordenador      ║ 19/10/16 ║  -9 ║
║ 4 ║ Averia ordenador      ║ 19/10/16 ║  53 ║
║ 5 ║ Averia sin determinar ║ 29/10/16 ║  46 ║
║ 5 ║ Averia sin determinar ║ 29/10/16 ║  10 ║
║ 5 ║ Averia sin determinar ║ 29/10/16 ║  44 ║
║ 5 ║ Averia sin determinar ║ 29/10/16 ║  10 ║
║ 5 ║ Averia sin determinar ║ 29/10/16 ║   1 ║
║ 5 ║ Averia sin determinar ║ 29/10/16 ║  63 ║
║ 6 ║ Averia conexion       ║ 10/01/17 ║ 119 ║
║ 6 ║ Averia conexion       ║ 10/01/17 ║  83 ║
║ 6 ║ Averia conexion       ║ 10/01/17 ║ 117 ║
║ 6 ║ Averia conexion       ║ 10/01/17 ║  83 ║
║ 6 ║ Averia conexion       ║ 10/01/17 ║  74 ║
║ 6 ║ Averia conexion       ║ 10/01/17 ║ 136 ║
╚═══╩═══════════════════════╩══════════╩═════╝
Your subquery is not correlated to the outer query, so it multiplies the rows by the number of rows it returns. Also, in my opinion, using the same table names multiple times without aliases at multiple query levels is bad practice, as it may cause some unexpected "surprises". Your query should be something like this:

SELECT 
  i1.ID_INCIDENCIA AS Incidencia,
  i1.DESCRIPCION,
  s1.FECHA,
  s1.FECHA - Inicio.Fecha AS Dias
FROM
  INCIDENCIA i1,
  SITUACION s1,
  (SELECT i2.ID_INCIDENCIA as Inicio_ID_INCIDENCIA, s2.FECHA AS Fecha 
    FROM SITUACION s2, INCIDENCIA i2
    WHERE s2.ID_ESTADO = '1' AND i2.ID_INCIDENCIA = s2.ID_INCIDENCIA) Inicio
WHERE
  s1.ID_ESTADO = '3' AND
  i1.ID_INCIDENCIA = s1.ID_INCIDENCIA
  AND Inicio.Inicio_ID_INCIDENCIA = i1.ID_INCIDENCIA
ORDER BY
  i1.ID_INCIDENCIA ASC


Added aliases everywhere + ID_INCIDENCIA inside the subquery, so it can be joined to the outer query based on that column.