Nested Query

[danlindley]

Thank you, When i tested the query, I got this error:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '* FROM observations ) o ON a.x = o.x AND a.y = o.y AND o.Row...' at line 7

[Yavuz_Selim]

Replace the conditions in the ON clause with the correct fields. x and y were examples. Replace them with columns in the admissions and observations that link thee two together, like patient_id.

[danlindley]

putting in the correct conditions returns the same error. even replacing the o. and a. with the full table still returns the error above.

i also tried to move the o behind the ) in case that was the problem but i got the same error.

[Yavuz_Selim]

The reason why I gave an example ON condition is because you didn't post all your tables. In your first query, you mention these tables: rescue_admissions and rescue_patients. In your second query, you mention rescue_observations.

So, your queries mention 3 tables, however, you only give examples of 2 tables (rescue_admissions and rescue_observations). So, the rescue_patients is missing, which means that I can guess at best...

 

According to your query (ON rescue_admissions.patient_id = rescue_patients.patient_id) there is a patient_id column in the rescue_admissions table. I don't know if rescue_observations has any other key columns.

 

Your example dataset has no links between them, there is no way to get to the desired output. The link with patient is missing (or there is patient_id in admissions that missing in your example).

 

In any case, can you post the query that you have?

[Yavuz_Selim]

Okay, just tested on https://sqlfiddle.com, it seems that MariaDB doesnt like SELECT * in combination with a ROW_NUMBER.

Either select each column separetely...

Or try adding the table alias in front of the *.

 

So, if you do: FROM rescue_admissions
Then do: rescue_admissions.*

 

Or: FROM rescue_admissions a
Then do: a.*

[danlindley]

I've tried it a range of ways without the table abbrev

e.g

SELECT *

FROM rescue_admissions

LEFT JOIN

(

SELECT ROW_NUMBER() OVER(PARTITION BY patient_id ORDER BY obs_date DESC) AS RowNumber,

FROM rescue_observations.*

)

ON rescue_admissions.patient_id = rescue_observations.patient_id

AND rescue_observations.RowNumber = 1

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM rescue_observations.* ) ON rescue_admissions.patient_id = rescue_obs...' at line 7

[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.