SQL hierarchy - getting newest grandchild

Transact-SQL
1

I have a H2 database with a LEFT JOIN query on Bird which returns all birds along with the newest Health_Check.Catch_Date . I want to extend this query and include in the results newest PIT.ID, PIT.CODE and TRANSMITTER.IDc, TRANSMITTER.CHANNEL which are related to Health_Check .

Note : not every Health_Check has a relationship to a Transmitter or PIT.

BIRD
BIRD985×1213 43.7 KB

A few notes about how the data is structured.

  • Most birds have several health checks;
  • Not every health check has a associated PIT or TRANSMITTER;
  • The newest Health_Check for a bird may not contain a PIT or TRANSMITTER;
  • Most birds have one PIT;
  • Most birds have a few Transmitters;
  • The date the Transmitter or PIT was attached is based on the related Health_Check.Catch_Date;
  • Most of my queries will require returning a Bird with the newest Health Check, PIT and Transmitter.

The SQL to get the newest HEALTH_CHECK is:

SELECT b.NAME, b.ID as birdId, hc1.CATCH_DATE, hc1.id as healthCheckId
FROM BIRD b
         LEFT OUTER JOIN
     (  HEALTH_CHECK hc1
         INNER JOIN
         (
             SELECT BIRD_ID, MAX(CATCH_DATE) AS MAX_DATE
             FROM HEALTH_CHECK
             GROUP BY BIRD_ID
         ) hc2
         ON hc2.BIRD_ID = hc1.BIRD_ID AND
            hc2.MAX_DATE = hc1.CATCH_DATE
         )
     ON hc1.BIRD_ID = b.ID;

Question : how can I also return in the results the most recent PIT and TRANSMITTER? NB: The newest PIT and TRANS is often not on the newest HealthCheck. I am open to a schema change/adding a second relationship but I must be able to determine which HealthCheck a PIT and TRANS was attached in.

As an example results would look like:

BIRD.ID  |  BIRD.NAME  | NEWEST HEALTH CHCECK | PIT.ID | PIT.CODE | TRANS.ID | TRANS.CHNL |
---------|-------------|----------------------|--------|----------|----------|------------|
   1     |    Bob      |    2022-03-01        |  AB001 |     3    |    2     |     40     |
   2     |    Jim      |      NULL            |  NULL  |    NULL  |   NULL   |    NULL    |
   3     |    Jane     |    2022-01-02        |  DC123 |     2    |    3     |     50     |

Please note that the newest PIT and newest TRANSMITTER is unlikely to be on the newest health check, so while my SQL above helps me get the newest healthcheck it doesnt help me with newest PIT or TRNASMITTER which may not be on newest health check.

I am open to a schema change.

2 
SELECT b.NAME, b.ID as birdId, health.CATCH_DATE AS NEWEST_HEALTH_CHECK,
pit.PIT_ID AS [PIT.ID], pit.PIT_CODE AS [PIT.CODE],
xmit.XMIT_ID AS [TRANS.ID], xmit.XMIT_CHANNEL AS [TRANS.CHNL]
FROM BIRD b
OUTER APPLY(SELECT TOP 1 h.CATCH_DATE, h.ID HEALTH_CHECK_ID
    FROM HEALTH_CHECK h WHERE h.BIRD_ID=b.ID ORDER BY h.CATCH_DATE DESC) health
OUTER APPLY(SELECT TOP 1 p.ID PIT_ID, p.CODE PIT_CODE, h.CATCH_DATE PIT_DATE
    FROM HEALTH_CHECK h 
    INNER JOIN PIT p on h.ID=p.HEALTH_CHECK_ID
    WHERE h.BIRD_ID=b.ID ORDER BY h.CATCH_DATE DESC) pit
OUTER APPLY(SELECT TOP 1 t.ID XMIT_ID, t.CHANNEL XMIT_CHANNEL, h.CATCH_DATE XMIT_DATE
    FROM HEALTH_CHECK h 
    INNER JOIN TRANSMITTER t on h.ID=t.HEALTH_CHECK_ID
    WHERE h.BIRD_ID=b.ID ORDER BY h.CATCH_DATE DESC) xmit

That should get you the results you posted. I also added dates for PIT and TRANSMITTER in case you need them, if not you can remove them from the OUTER APPLY.

Edit: in looking at your screenshots, it appears you're not using Microsoft SQL Server. (apologies for missing this). SQLTeam is an MS SQL Server focused site. The code I posted will work for SQL Server, but the syntax may not be the same as your product.

Edit 2: I've reviewed the H2 database docs, the OUTER APPLY should be replaceable with LEFT JOIN:

SELECT b.NAME, b.ID as birdId, health.CATCH_DATE AS NEWEST_HEALTH_CHECK,
pit.PIT_ID AS [PIT.ID], pit.PIT_CODE AS [PIT.CODE],
xmit.XMIT_ID AS [TRANS.ID], xmit.XMIT_CHANNEL AS [TRANS.CHNL]
FROM BIRD b
LEFT JOIN (SELECT TOP 1 b.ID BIRD_ID, h.CATCH_DATE, h.ID HEALTH_CHECK_ID
    FROM HEALTH_CHECK h WHERE h.BIRD_ID=b.ID ORDER BY h.CATCH_DATE DESC) health 
    ON b.ID=health.BIRD_ID
LEFT JOIN (SELECT TOP 1 b.ID BIRD_ID, p.ID PIT_ID, p.CODE PIT_CODE, h.CATCH_DATE PIT_DATE
    FROM HEALTH_CHECK h 
    INNER JOIN PIT p on h.ID=p.HEALTH_CHECK_ID
    WHERE h.BIRD_ID=b.ID ORDER BY h.CATCH_DATE DESC) pit
    ON b.ID=pit.BIRD_ID
LEFT JOIN (SELECT TOP 1 b.ID BIRD_ID, t.ID XMIT_ID, t.CHANNEL XMIT_CHANNEL, h.CATCH_DATE XMIT_DATE
    FROM HEALTH_CHECK h 
    INNER JOIN TRANSMITTER t on h.ID=t.HEALTH_CHECK_ID
    WHERE h.BIRD_ID=b.ID ORDER BY h.CATCH_DATE DESC) xmit
    ON b.ID=xmit.BIRD_ID

I don't have a way to test this, and can't determine if a correlated subquery is valid in H2.

3

Thank you for the helpful reply.
It didn't work as pasted, but I am trying to understand the query.
In the first line you have health.CATCH_DATE but I do not see where the alias health is set to anything?

4

The first subquery is aliased as "health", you may have to scroll the code window to see it.

What error did you get? In what way did it not work?