Can a join do this?

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?

if each power supply can have multiple devices, and a device can belong to only one power supply…

then, sure

3 Likes