Simplified version of problem. Suppose there are three tables that need to be joined.:

CREATE TABLE readings (
id INT(11) NOT NULL AUTO_INCREMENT,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIME,
made_by VARCHAR(30),
PRIMARY KEY (id)
);

CREATE TABLE temp_reading (
id INT(11) NOT NULL AUTO_INCREMENT,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIME,
reading FLOAT,
location_ID INT(11),
reading_id INT(11),
PRIMARY KEY (id)
FOREIGN KEY (location_id) REFERENCES location (id) ON DELETE SET NULL,
FOREIGN KEY (reading_id) REFERENCES readings (id) ON DELETE SET NULL,
);

CREATE TABLE location (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(30),
PRIMARY KEY (id)
);

A reading can have multiple temp readings. I’d like to join the readings and temp readings, with each reading having its own column in the new join based on the name or ID of the location. For example

id, created_at, made_by, reading_location1, reading_location1_foreign_key, reading_location2, reading_location2_foreign_key

SELECT readings.*, temp_reading.reading AS temp_reading, temp_reading.location_ID  AS reading_location1_foreign_key FROM readings RIGHT JOIN temp_reading ON temp_reading.readings_id = readings.id;

and I think I need to add something like:

RIGHT JOIN location.name ON location.id = temp_reading.location_ID AS (column reading_location)

This gives me a separate row for each reading. Whereas I’d like it to be all in one row with separate columns for the values associated to the temp readings and the location associated to the temp reading. A bit confusing, I know.

There will be a fixed number of locations (2).