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
Then I have another table
Tratamiento , which contains the fields
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%
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!