Is this possible with a join?

Databases
1

Im trying to come up with a select statement to retrieve the title, and pdu_id if I have the device_id, heres my tables

create table devices (
   device_id INT NOT NULL AUTO_INCREMENT,
   power_distributor_id SMALLINT NULL,
   rack_id INT NOT NULL,
   orientation CHAR(1) DEFAULT '1',
   beginning_slot DECIMAL(3,1) NOT NULL,
   ending_slot DECIMAL(3,1) NOT NULL,
   title VARCHAR(100) NOT NULL,
   notes TEXT DEFAULT NULL,
   width INT DEFAULT NULL,
   beginning_x INT DEFAULT NULL,
   power_requirement DECIMAL(6,2) DEFAULT 16.9,
   created_by VARCHAR(50) DEFAULT 'Admin',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   enabled BOOLEAN DEFAULT 1,
   updated_by VARCHAR(50) NULL,
   updated_date TIMESTAMP 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,
   power_supply_id SMALLINT,
   recepticle_type TINYINT,
   number_of_outlets TINYINT,
   linking_type SMALLINT,
   linking_title VARCHAR(25),
   notes TEXT NULL,
   created_by VARCHAR(50) DEFAULT 'Admin',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_by VARCHAR(50) NULL,
   updated_date TIMESTAMP NULL,
   enabled BOOLEAN DEFAULT 1,
   FOREIGN KEY ( power_supply_id ) REFERENCES power_supplies (power_supply_id),   
   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 )
);

I would think that this is possible because the devices table is linked to the power_distributors table via the power_distributor_id, then the primary key of the power-distributors table is the foreign key in the pdus table.
So a complicated JOIN is needed like

SELECT pdus.title, pdus.pdu_id 
FROM pdus 
INNER JOIN power_distributors 
ON pdus.power_distributor_id = power_distributors.power_distributor_id

But then its incomplete as I dont know the rest (how to find the power_distributor_id given a device_id)

2

power_distributor_id is on devices, so in reality, you don’t need power_distributors at all

You’ve joined multiple tables before so I’m not quite sure why this is complicated.

SELECT a.field1
     , c.field2
  FROM tableA a
 INNER JOIN tableB b ON a.fieldx = b.fieldx
 INNER JOIN tableC c ON b.fieldx = c.fieldx
WHERE a.field10 = aa

though in your case, you can skip the middle table.

SELECT a.field1
     , c.field2
  FROM tableA a
 INNER JOIN tableC c ON c.fieldx = c.fieldx
WHERE a.field10 = aa
1 Like
3

is a and c aliases to those tables and what is aa?

4

Yes, a and c are aliases.

What do you think aa would represent? The value you are searching for…

5

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.