This query
SELECT racks.rack_id,
title, manufacturer, room_id, model, enabled, created_date, total_power_requirement
FROM racks LEFT JOIN (
SELECT rack_id, SUM(power_requirement) AS total_power_requirement
FROM devices
GROUP BY rack_id) AS tpr ON tpr.rack_id = racks.rack_id LIMIT 0, 10
works great
I understand this query cause the two tables (devices and racks share a rack_id)
create table racks (
rack_id INT NOT NULL AUTO_INCREMENT,
...
...
PRIMARY KEY ( rack_id )
);
create table devices (
device_id INT NOT NULL AUTO_INCREMENT,
power_distributor_id SMALLINT NULL,
rack_id INT NOT NULL,
...
...
FOREIGN KEY (rack_id) REFERENCES racks (rack_id),
PRIMARY KEY ( device_id )
);
But is there a way to find the total_power_requirement from the devices table, if I add another table into the mix?
create table power_supplies (
power_supply_id SMALLINT NOT NULL AUTO_INCREMENT,
...
...
PRIMARY KEY ( power_supply_id )
);
I would not think this is possible cause the devices table is not connected to the power_supplies table in any nway.
so should I create a FK in the devices table to point to the power_supplies table?