Bring data from a table whose date is less than the smallest in another table

Question

Bring data from a table whose date is less than the smallest in another table

Navigation

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

4

I want to do an SQL query to see certain 'inconsistencies' in a SQLServer database.

To simplify it a bit, let's say that I have a table Movimiento_Paciente that contains a field pacienteId and another fechaInicio . In this table there may be several movements of a patient, that is, there may be several records with fechaInicio different for the same pacienteId .

Then I have another table Tratamiento , which contains the fields nombre_tratamiento , pacienteId and fechaTratamiento . Like the previous table, there may be several treatments with different dates for the same patient.

What I want to look for are patients whose fechaTratamiento minimum is less than the fechaInicio minimum in the% Movimiento_Paciente .

What I tried was something like that ...

SELECT t1.pacienteId FROM Movimiento_Paciente t1 
INNER JOIN Tratamiento t2 ON t1.pacienteId = t2.pacienteId 
WHERE (SELECT MIN(fechaTratamiento) 
FROM Tratamiento 
GROUP BY pacienteId) < (SELECT MIN(fechaInicio) FROM Movimiento_Paciente 
GROUP BY pacienteId)

That query gives me an error because obviously those select return more than one value.

I'm a bit stuck and I do not know how to do it. Can you help me?

Thank you very much, best regards!

sql join consulta-sql

asked by Tamaran 25.10.2018 в 15:15

source

4 answers

How to create the relationship of two tables in laravel lumen? How can I take a break when writing a file on Android?

score 2 · Answer 1

Given a patient, you can obtain the minimum treatment date in the following way:

select min(fechaTratamiento)
from Tratamiento T
where T.pacienteId = @pacienteId

The same for the minimum start date ...

What remains then is to compare both dates:

select pacienteId
from Movimiento_Paciente A
where (select min(fechaTratamiento)
       from Tratamiento T
       where T.pacienteId = A.pacienteId
      ) < (select min(fechaInicio) from
           Movimiento_Paciente B
           where A.pacienteId = B.pacienteId)

That query gives you the list of patients you're looking for.

score 1 · Answer 2

One way is to use subqueries to get the minimum date for the two tables and compare them by JOIN

SELECT  t1.pacienteId 
    FROM (SELECT    pacienteId,
            MIN(fechaInicio)                    AS 'fechaInicio'
            FROM Movimiento_Paciente
            GROUP BY pacienteId
        ) t1 
    INNER JOIN (SELECT  pacienteId,
                        MIN(fechaTratamiento)   AS 'fechaTratamiento'
            FROM Tratamiento
        ) t2 
        ON t1.pacienteId = t2.pacienteId 
    WHERE t2.fechaTratamiento < T1.fechaInicio

This should return all pacienteId of Movimiento_Paciente where the minimum date fechaInicio is later than the minimum date fechaTratamiento of Tratamiento .

score 0 · Answer 3

If what you want is to return a value you must pass the id of the patient you have in the query and verify that they are the same, more or less your code should be like this,

SELECT 
t1.pacienteId 
FROM Movimiento_Paciente t1 
INNER JOIN Tratamiento t2 ON t1.pacienteId = t2.pacienteId 
WHERE (SELECT MIN(fechaTratamiento) FROM Tratamiento where pacienteId = t1.pacienteId  GROUP BY pacienteId) < 
(SELECT MIN(fechaInicio) FROM Movimiento_Paciente where pacienteId = t1.pacienteId GROUP BY pacienteId)

I hope it works for you.

score 0 · Answer 4

I propose an alternative to nested selects, which can be slower if the tables have many records, is to use having.

Schema (PostgreSQL v10.0)

CREATE TABLE Movimiento_Paciente (
  pacienteId INT,
  fechaInicio DATE
);
INSERT INTO Movimiento_Paciente (pacienteId, fechaInicio) VALUES (1,'2018-01-01');
INSERT INTO Movimiento_Paciente (pacienteId, fechaInicio) VALUES (1,'2018-02-01');
INSERT INTO Movimiento_Paciente (pacienteId, fechaInicio) VALUES (2,'2018-01-10');
INSERT INTO Movimiento_Paciente (pacienteId, fechaInicio) VALUES (2,'2018-02-01');

CREATE TABLE Tratamiento (
  pacienteId INT,
  fechaTratamiento DATE
);

INSERT INTO Tratamiento (pacienteId, fechaTratamiento) VALUES (1,'2018-01-10');
INSERT INTO Tratamiento (pacienteId, fechaTratamiento) VALUES (1,'2018-02-10');
INSERT INTO Tratamiento (pacienteId, fechaTratamiento) VALUES (2,'2018-01-01');
INSERT INTO Tratamiento (pacienteId, fechaTratamiento) VALUES (2,'2018-02-01');

Query # 1

SELECT T.pacienteId , MIN(T.fechaTratamiento),
MIN(M.fechaInicio)

FROM Tratamiento T 
INNER JOIN Movimiento_Paciente M ON M.pacienteId = T.pacienteId
GROUP BY T.pacienteId
HAVING MIN(T.fechaTratamiento)<MIN(M.fechaInicio)
;

| pacienteid | min                      | min                      |
| ---------- | ------------------------ | ------------------------ |
| 2          | 2018-01-01T00:00:00.000Z | 2018-01-10T00:00:00.000Z |

View on DB Fiddle