Using UNION in MySQL is not properly combining the rows

I have a patients table (kid) and 2 tables to track both their height and weight in medical routine controls (track_height, track_weight).

• there may be more than 1 measurement per type (height and weight) on a given month.
• some months might have no measurements at all.
• only months that have BOTH measurements (height and weight), should be presented in the results.
• if there are multiple measurements of any type on any given month, they need to be averaged so that, at best, there is only one result per measurement / per month.

This is my current query:

(SELECT CONCAT(YEAR(track_height.date_track), '-', LPAD(MONTH(track_height.date_track), 2, 0)) AS date_track, ROUND(AVG(track_height.height), 1) AS height, NULL AS weight
FROM track_height
WHERE track_height.id_kid = 17
GROUP BY YEAR(track_height.date_track), MONTH(track_height.date_track)
ORDER BY track_height.date_track)

UNION

(SELECT CONCAT(YEAR(track_weight.date_track), '-', LPAD(MONTH(track_weight.date_track), 2, 0)) AS date_track, NULL AS height, ROUND(AVG(track_weight.weight), 1) AS weight
FROM track_weight
WHERE track_weight.id_kid = 17
GROUP BY YEAR(track_weight.date_track), MONTH(track_weight.date_track)
ORDER BY track_weight.date_track)

ORDER BY date_track

The problem is that it's being presented like this:

And it should be presented like this:

As you can see, there are 2 specific issues that need to be solved:

• The UNION statement is not combining the results by date. There must be only one YYYY-MM row with both height and weight averaged values for that month.
• In this case, February (2023-02) should not be printed, because we're missing the weight tracking data for that month.

I've set a sqlfiddle example, but in case you want to take a look at the schema, here it is:

CREATE TABLE kid (
  id_kid int(10) UNSIGNED NOT NULL,
  date_insert timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  alias varchar(30) NOT NULL,
  birthday date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO kid (id_kid, date_insert, id_user, alias, gender, birthday, avatar) VALUES (17, '2023-10-22 17:19:08', 'Jenny', '2020-09-30');

CREATE TABLE track_height (
  id_track int(10) UNSIGNED NOT NULL,
  id_kid int(10) UNSIGNED NOT NULL,
  date_track date NOT NULL,
  height float UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO track_height (id_track, id_kid, date_track, height) VALUES (45, 17, '2023-04-07', 50), (46, 17, '2023-04-30', 52), (101, 17, '2023-01-31', 25), (102, 17, '2023-02-28', 34), (103, 17, '2023-03-31', 48), (104, 17, '2023-03-15', 42), (105, 17, '2023-03-01', 40), (106, 17, '2023-05-17', 55);

CREATE TABLE track_weight (
  id_track int(10) UNSIGNED NOT NULL,
  id_kid int(10) UNSIGNED NOT NULL,
  date_track date NOT NULL,
  weight float UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO track_weight (id_track, id_kid, date_track, weight) VALUES (34, 17, '2023-01-09', 9.6), (35, 17, '2023-01-18', 9.9), (36, 17, '2023-01-31', 10.1), (39, 17, '2023-03-07', 10.5), (40, 17, '2023-03-16', 10.6), (41, 17, '2023-04-05', 10.7), (42, 17, '2023-05-04', 11), (43, 17, '2023-05-30', 11.5);

Thanks for your help!

Here's what I would do,

select date_format(h.date_track, "%Y-%m"), 
round(avg(h.height), 1) as height, 
round(avg(w.weight), 1) as weight
from track_height h inner join track_weight w 
on h.id_kid = w.id_kid and month(h.date_track) = month(w.date_track)
group by month(h.date_track)
order by month(h.date_track); 

inner join would make sure that only months that are present in both tables are considered.

Is there any reason for you to use 'Union'?