Join 2 or 3 tables?

[lurtnowski]

I have 4 tables

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 )
);


create table power_distributors (
   power_distributor_id SMALLINT NOT NULL AUTO_INCREMENT,
...
...
   PRIMARY KEY ( power_distributor_id )
);


create table pdus (
   pdu_id SMALLINT NOT NULL AUTO_INCREMENT,
   power_distributor_id SMALLINT,
   title VARCHAR(25),
   FOREIGN KEY ( power_distributor_id ) REFERENCES power_distributors (power_distributor_id),   
   PRIMARY KEY ( pdu_id )
);

create table upss (
   ups_id SMALLINT NOT NULL AUTO_INCREMENT,
   power_distributor_id SMALLINT,
   title VARCHAR(25),
   FOREIGN KEY ( power_distributor_id ) REFERENCES power_distributors (power_distributor_id),   
   PRIMARY KEY ( ups_id )
);

I have the device_id and am trying to get everything from the devices table and the title from the upss table or from the pdus table
Because there connected, would this work?

SELECT *, upss.title, pdus.title  
FROM ((devices
INNER JOIN pdus ON devices.power_distributor_id = pdus.power_distributor_id )
INNER JOIN upss ON devices.power_distributor_id = upss.power_distributor_id )
WHERE device_id = 7;

the result is nothing. Is there a way for the query to only use 1 of the joins?

[rpkamp]

You could LEFT JOIN on both and see which title is filled, but really the database design is wrong here. You should have used a supertype table with subtype tables as you were told before, then you wouldn’t have this problem.

The main problem you’re having is that a device can be linked to both a UPS as well as a PDU in your schema. While in the real world this is not possible.

Make the impossible inexpressible. Meaning, if its not possible in the real world, it should not be possible in your model.

Because what happens if through some bug a device is connected to both a UPS as well as a PDU in your database? Obviously one of those is wrong. But which one? How could you tell?

[lurtnowski]

I thought I did it that way, heres the main one

create table power_distributors (
   power_distributor_id SMALLINT NOT NULL AUTO_INCREMENT,
   recepticle_type TINYINT,
   manufacturer VARCHAR(50) DEFAULT 'Urtnowski Manufacturing Co',
   model VARCHAR(25),
   voltage DECIMAL(8,2),
   number_of_outlets TINYINT,
   linking_type SMALLINT,
   linking_title VARCHAR(25),
   linking_id SMALLINT,
   notes TEXT NULL,
   created_by VARCHAR(50) DEFAULT 'lurtnowski@industechnology.com',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_by VARCHAR(50) NULL,
   updated_date TIMESTAMP NULL,
   enabled BOOLEAN DEFAULT 1,  
   PRIMARY KEY ( power_distributor_id )
);

then, here are my two sub-types

create table pdus (
   pdu_id SMALLINT NOT NULL AUTO_INCREMENT,
   power_distributor_id SMALLINT,
   title VARCHAR(25),
   FOREIGN KEY ( power_distributor_id ) REFERENCES power_distributors (power_distributor_id),   
   PRIMARY KEY ( pdu_id )
);

create table upss (
   ups_id SMALLINT NOT NULL AUTO_INCREMENT,
   power_distributor_id SMALLINT,
   title VARCHAR(25),
   FOREIGN KEY ( power_distributor_id ) REFERENCES power_distributors (power_distributor_id),   
   PRIMARY KEY ( ups_id )
);

Did I make those relationships wrong?

[rpkamp]

The pdus table and the upss table look exactly the same. I don’t see a real reason to split them.

Why not add title and type (enum of pdu and ups) to the power_distributors table? That would make it a lot easier.

Unless maybe you expect pdus and upss to become different over time?

[ahundiak]

I don’t understand the parens in your query. Something like:

SELECT *, upss.title, pdus.title 
FROM devices 
LEFT JOIN pdus ON devices.power_distributor_id = pdus.power_distributor_id 
LEFT JOIN upss ON devices.power_distributor_id = upss.power_distributor_id
WHERE device_id = 7;

Would get your started. If the relations are not 1:1 then you might end up with more records than you expect. You might also get a complaint about the * if you have some duplicate column names in which case you need to state exactly which device columns you want.

[Mittineague]

@rpkamp

The pdus table and the upss table look exactly the same

If this is so both the PKs will be Indexes, a field can be Indexed without being a PK. If this is the sole motivation, my guess would be that any (negligible) benefit of having separate tables would be offset by the increased complexity of queries. Don’t be afraid to use what tools are available.