Question 186151 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL Retrieving values with intermediary table

I have the following tables:

MONKEY :

id name
1 John
2 Mary

CHECK :

id monkey_id checkdate
1 1 2023-06-02 03:00:00
2 2 2023-08-24 14:30:00
3 1 2023-06-04 14:35:00

MEASUREMENTS :

id check_id length width weight
1 1 23.34 6.34 3.2
2 1 23.87 6.22 NULL
3 1 24.3 6.10 NULL
4 2 27.8 7.4 NULL
5 2 27.56 7.62 NULL

XMITTER :

id check_id channel tune attachstart attachfinish removed
1 3 534 2 2023-08-24 14:30:32 2023-08-24 14:45:00 NULL
2 1 532 1 2023-06-02 03:31:00 2023-06-02 03:35:00 2023-06-10 14:45:00

How would I retrieve a monkey with:

  1. Date of most recent check
  2. Multiple sets of length/width measurements are taken at the same time (and joined by check_id) – I would like to return the average of the most recent length/width measurements and weight which is usually only taken once during a check.
  3. The current transmitter if one is attached. A transmitter is attached if it doesnt have a removed date.

EXAMPLE RESULT SET :

monkey.id last_check_date newest_avg_length newest_avg_width newest_avg_width channel tune channel_attach_start
1 2023-06-04 23.82 6.20 3.2 532 1 2023-06-02

There should be one result set for each monkey.

I have created a fiddle here – which is same structure (slightly different data).

*NB: The most recent measurements is on check 1. The most recent xmitter is on check 3.

Tags:

2

Answers


  1. 0 
    SELECT
  m.id AS monkey_id,
  MAX(h.health_check_date) AS last_check_date,
  AVG(me.length) AS newest_average_length,
  AVG(me.width) AS newest_average_width,
  AVG(me.weight) AS newest_avg_weight,
  t.channel AS channel,
  t.offset AS tune,
  t.attach_start AS channel_attach_start
FROM
  monkeys m
 JOIN
  health_check h ON h.monkey_id = m.id
 JOIN
  measurements me ON me.health_check_id = h.id
 JOIN
  transmitter t ON t.health_check_id = h.id AND t.removed IS NULL

GROUP BY
  m.id, m.name
ORDER BY
  m.id;
    Login or Signup to reply.
  2. 0

    This query assumes:

    1. every monkey has at least one health_check and set of measurements
    2. there can only ever be zero or one transmitter attached per monkey
    SELECT
    latest_health_check.monkey_id,
    latest_health_check.last_check_date,
    latest_health_check.newest_avg_length,
    latest_health_check.newest_avg_width,
    latest_health_check.newest_avg_weight,
    tx.channel,
    tx.offset,
    DATE(tx.attach_start) AS channel_attach_start
FROM (
    -- Derived table for the latest check.
    SELECT
        hc.monkey_id,
        hc_max.last_check_date,
        AVG(length) AS newest_avg_length,
        AVG(width) AS newest_avg_width,
        AVG(weight) AS newest_avg_weight
    FROM health_check hc
    JOIN (
        SELECT monkey_id, MAX(health_check_date) AS last_check_date
        FROM health_check
        -- WHERE monkey_id = 2 /* Uncomment this line if for specific monkey */
        GROUP BY monkey_id
    ) hc_max
        ON hc.monkey_id = hc_max.monkey_id
        AND hc.health_check_date = hc_max.last_check_date
    JOIN measurements ms
        ON hc.id = ms.health_check_id
    GROUP BY hc.monkey_id
) latest_health_check
LEFT JOIN transmitter tx
    ON EXISTS (
        SELECT 1 FROM health_check hc
        WHERE hc.id = tx.health_check_id
        AND hc.monkey_id = latest_health_check.monkey_id
    )
    AND tx.removed IS NULL;

    And, here is an updated version of your fiddle

    P.S. It only returns for monkey_id 2, as this is the only monkey with measurements for its most recent check.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search