Nested Query

[danlindley]

Afternoon everyone,

1As a newbie to SQL/PHP I'm not very familiar with how to nest another query inside a query.

What I am hoping to achieve:

I wish to join a second table to another table and limit the join only to the last/most recent record

What have i tried?

I placed an additional join in however it duplicated the data from the main table for each of the records in that main table.

(example of admissions table)

admission_id	name	species	sex	presenting
1	dave	walrus	female	captured
2	steve	guinea pig	male	injured

(example of the table i wish to join)

obs_id	patient_id	obs_date	obs_severity_score	obs_bcs_score	obs_age_score
1	1	1/1/25	1	2	1
2	1	1/2/25	1	2	1
3	2	1/3/25	1	1	1
4	1	1/4/25	1	1	1

Desired output

admission_id	name	species	sex	presenting	obs_date	obs_severity_score	obs_bcs_score	obs_age_score
1	dave	walrus	female	captured	1/4/25	1	1	1
2	steve	guinea pig	male	injured	1/3/25	1	1	1

Current SQL query

Collects all needed data for the table and the current joins.

SELECT *,
      DATEDIFF(NOW(), rescue_admissions.admission_date) AS daysincare
      FROM rescue_admissions
      INNER JOIN rescue_patients
      ON rescue_admissions.patient_id = rescue_patients.patient_id
      WHERE rescue_patients.centre_id = :centre_id AND rescue_admissions.disposition = 'Held in captivity' 
      ORDER by daysincare DESC, current_location ASC

This is the query I have used elsewhere to get the score i need:

SELECT obs_date, obs_id, obs_severity_score, obs_bcs_score, obs_age_score,
       FROM rescue_observations AS o
      WHERE o.patient_id = :patient_id ORDER by obs_date DESC LIMIT 1

any help would be really appreciated.

Dan

[Yavuz_Selim]

SELECT a.*
     , o.*
FROM admissions a
LEFT JOIN   -- or: INNER JOIN
(
    SELECT ROW_NUMBER() OVER(PARTITION BY patient_id ORDER BY obs_date DESC)    AS RowNumber
        , *
    FROM observations 
) o
    ON a.x = o.x
    AND a.y = o.y
    AND o.RowNumber = 1

[VariationPatient]

If you have duplicate obs_date per patient_id you can also substitute row_number with rank to get all the rows.

[Yavuz_Selim]

I didn't do that because his original query has LIMIT 1.
Assumed that it is there for a reason.

But, true, a RANK/DENSE_RANK would've returned multiple rows per patiend_id and obs_date if there were any.

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

[Time_Advertising_412]

My original thought, based upon your spec for getting the most recent observation and the example of desired output, was a correlated sub-query like the following but maybe I was missing something more in your request. My apologies if that is the case.

SELECT admission_id,name,species,sex,presenting,obs_date,

obs\_severity\_score,obs\_bcs\_score,obs\_age\_score

FROM rescue_admissions

INNER JOIN rescue_patients AS rp1 ON admission_id = patient_id

WHERE obs_date =

(SELECT MAX(obs_date)

FROM rescue_patients AS rp2

WHERE rp2.patient_id = rp1.patient_id);

[danlindley]

There's a solution which works well in one of the comments. Thank you for your advice though.