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
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?