Set alias column in a select by another select-CodePudding

I need some help with mariadb queries. I'm trying to set an alias column with the result of another select.

I have 3 tables:

Units_dimension.
Units.
Readings.

Units_dimension table:

id	dimension
1	temperature
2	humidity
3	pressure

Units table:

id	dimension_id	unit	representation
1	1	Celsius	°C
2	1	Farenheit	°F
3	1	Kelvin	K
4	2	Percentage	%
5	3	HectoPascal	hPa

Readings table:

id	station_id	datetime	unit_id	value
1	ESP0001	2022-10-31 01:00:00.000	1	23.5
2	ESP0001	2022-10-31 01:00:00.000	4	79
3	ESP0001	2022-10-31 01:00:00.000	5	1019.6
4	ESP0001	2022-10-31 02:00:00.000	1	23.3
5	ESP0001	2022-10-31 02:00:00.000	4	79
5	ESP0001	2022-10-31 02:00:00.000	5	1019.6
...	...	...	...	...

I want to get the value column in a select with alias from the unit dimension.

Example SELECT r.datetime, r.value AS (?1) FROM readings r WHERE station_id = 'ESP0001' and unit_id = ?2

?1 is temperature or humidity or pressure, etc.

?2 is the unit_id.

I tried something like that:

SELECT 
    r.datetime, 
    r.value AS (SELECT ud.dimension FROM units u LEFT JOIN unit_dimension ud ON (ud.id = u.dimension_id) WHERE u.id = 1) 
FROM readings r 
WHERE r.unit_id = 1;

But I have an SQL Error [1064] [42000]: (conn=67) You have an error in your SQL syntax;

Thanks.

CodePudding user response：

I strongly believe You can simplify the whole query as below:

SELECT r.`datetime`, r.`value`, ud.`dimension`
  FROM readings r, units u, unit_dimension ud
 WHERE u.id = r.unit_id 
   AND ud.id = u.dimension_id
   AND r.station_id = 'ESP0001'
   AND r.unit_id = 1 -- Celsius

CodePudding user response：

Thanks for your comments. I think i'll try two querys, store the resulset into a variable and them combine the result.

Finally the querys (backend api in nodejs):

The first query result returns the dimension. If the unit id is not correct the response will be 404.

SELECT ud.dimension 
FROM units u, unit_dimension ud 
WHERE ud.id = u.dimension_id 
AND u.id = ${req.params.unit_id};

The second query:

SELECT r.datetime, r.value AS ${dimension}, u.representation 
FROM readings r, units u, unit_dimension ud
WHERE u.id = r.unit_id 
AND ud.id = u.dimension_id
AND r.station_id = '${req.params.station_id}'
AND r.unit_id = ${req.params.unit_id};