Inner join and INT field, metric not working

Grafana MySQL
1

EDIT: Solved below.

Grafana 9.4.7 in Docker, accessed via Windows 10.

Trying to inner join and use new column as metric

Table: timesheet
Field: user (int)
Field: duration (int)
Field: start_time (datetime)

Table: users
Field: id (int)
Field: username (varchar(180))
Field: alias (varchar60))

Step by step:

From table timesheet, the start_time and duration fields result in a proper graph, however I want to split this out per user. But user is not rendered as metric.

SELECT
  user "metric",
  start_time AS "time",
  duration AS "value"
FROM timesheet

image

I guessed it is because the field is INT type. When converting it to CHAR, it does work:

SELECT
  CONVERT (user, char) AS "metric",
  start_time AS "time",
  duration AS "value"
FROM timesheet
ORDER BY
  start_time

image

This feels like a bad method, but anyways, the numbers should be the according names from the other table, so going for a join.

SELECT
  t.user AS "t.id",
  t.start_time AS "time",
  t.duration AS "value",
  u.id AS "u.id",
  u.username AS "metric"
FROM timesheet t
INNER JOIN users u ON u.id = t.user;

A table renders as expected, with all the columns.
The graph looks like this:
image
Using transform to hide t.id and u.id does not result in a split out metric, as in, a value per username. Applying the convert trick to t.id and u.id this time does not result in a value per username as well.

The result I am looking for is this:
image
(Mock up via Fx dev tools.)

How to accomplish this?

2

I did something wrong. While working with two queries, I forgot to put the second one to “Format: Time series”.
After this an error shows, indicating that ORDER BY is required.

SELECT
  user AS "t.id",
  t.start_time AS "time",
  t.duration AS "value",
  u.id AS "u.id",
  u.username AS "metric"
FROM timesheet t
INNER JOIN users u ON u.id = t.user
ORDER BY
  time

This query works, and shows users split out, but also shows t.id and u.id, and that for every user.

A subquery is the ultimate step in solving the issues:

SELECT metric,time,value FROM
(SELECT
  user AS "t.id",
  t.start_time AS "time",
  t.duration AS "value",
  u.id AS "u.id",
  u.username AS "metric"
FROM timesheet t
INNER JOIN users u ON u.id = t.user
)X
ORDER BY
  time

Edit:
Found out today that it does not have to be a subquery because it is not necessary to SELECT the column on which the join will happen.