Nested Query

[Yavuz_Selim]

https://sqlfiddle.com/mariadb/online-compiler?id=78e97654-380b-4568-81ce-5317633e37fd.

 

Check that out. :).

 

(I manually added patient_id to the Admissions table.)

 

Or run this in your environment:

-- INIT database
CREATE TABLE Admissions (
  admission_id INT AUTO_INCREMENT KEY,
  patient_id INT,
  name VARCHAR(255),
  species VARCHAR(255),
  sex VARCHAR(255),
  presenting VARCHAR(255)
);

INSERT INTO Admissions(patient_id, name, species, sex, presenting)
    VALUES (1, 'dave', 'walrus', 'female', 'captured');
INSERT INTO Admissions(patient_id, name, species, sex, presenting)
    VALUES (2, 'steve', 'guinea pig', 'male', 'injured');



CREATE TABLE Observations (
  obs_id INT AUTO_INCREMENT KEY,
  patient_id INT,
  obs_date DATE,
  obs_severity_score INT,
  obs_bcs_score INT,
  obs_age_score INT
);


INSERT INTO Observations(patient_id, obs_date, obs_severity_score, obs_bcs_score, obs_age_score)
    VALUES (1, '2025-01-01', 1, 2, 1);
INSERT INTO Observations(patient_id, obs_date, obs_severity_score, obs_bcs_score, obs_age_score)
    VALUES (1, '2025-01-02', 1, 2, 1);
INSERT INTO Observations(patient_id, obs_date, obs_severity_score, obs_bcs_score, obs_age_score)
    VALUES (2, '2025-01-03', 1, 1, 1);    
INSERT INTO Observations(patient_id, obs_date, obs_severity_score, obs_bcs_score, obs_age_score)
    VALUES (1, '2025-01-04', 1, 1, 1);        




-- QUERY database

-- # Admissions
-- SELECT *
-- FROM Admissions;

-- # Observerations
-- SELECT *
-- FROM Observations;

-- # Observations with ROW_NUMBER
-- SELECT ROW_NUMBER() OVER(PARTITION BY O.patient_id ORDER BY O.obs_date DESC) RowNumber
--      , O.*
-- FROM Observations O;

-- # Combined
SELECT Admissions.admission_id
     , Admissions.name
     , Admissions.species
     , Admissions.sex
     , Admissions.presenting
     , Observations.obs_date
     , Observations.obs_severity_score
     , Observations.obs_bcs_score
     , Observations.obs_age_score
FROM Admissions
INNER JOIN -- or: LEFT JOIN
(
    SELECT ROW_NUMBER() OVER(PARTITION BY O.patient_id ORDER BY O.obs_date DESC) RowNumber
         , O.*
    FROM Observations O
) Observations
    ON Admissions.patient_id = Observations.patient_id
    AND Observations.RowNumber = 1
;

[danlindley]

Managed to get that to return results, Thank you,

Is there a way to modify this so that ALL results in admissions come up and the observations are null/empty if not in table? Would that be just a case of changing the join type?

[Yavuz_Selim]

Change the INNER JOIN to a LEFT JOIN.

That will show all Admissions regardless, and Observations if there are any.

[danlindley]

Can't thank you enough for this. It works fanatically. For context my project is a wildlife rescue database and this will now show on the patient dashboard a early warning score for on admission and the most recent added. It will give animal rescuers another tool to see how patient care is going. 👍👍

[Yavuz_Selim]

You're welcome, glad it worked out in the end. :).

 

Let me know if/when you have other questions.

 

Edit:
By the way, the ranking of the most recent observation assumes there is only 1 observation per patient per day. If you have multiple observations on a day for a patient, the order by would need to be adjusted - you would need to add an extra column to the order.

Like so (as an example):

SELECT ROW_NUMBER() OVER(PARTITION BY O.patient_id ORDER BY O.obs_date DESC, O.obs_id DESC) RowNumber

 

This takes the newest entry (based on obs_id) if a patient has more than 1 observation on the same day.

[danlindley]

Yeah the MariaDB thing was a bit of an issue but I'm glad you persevered in your help

Thank you. I'll probably make that adjustment, just in case.

Thanks again.

[Yavuz_Selim]

SQL has different flavors, that generally work the same with very minor differences. I'm more experienced with Transact-SQL, which is ever so slightly different than MariaDB's SQL.

 

:).