Query help

Databases
1

I have these tables

create table racks (
   rack_id INT NOT NULL AUTO_INCREMENT,
   location VARCHAR(100) NOT NULL,
   elevation VARCHAR(100) NULL,
   title VARCHAR(100) NOT NULL,
   power_panel VARCHAR(100) NOT NULL,
   circuit_breaker VARCHAR(10) NOT NULL,
   number_of_slots INT NOT NULL,
   display CHAR(1) DEFAULT '1',
   PRIMARY KEY ( rack_id )
);

create table devices (
   device_id INT NOT NULL AUTO_INCREMENT,
   rack_id INT NOT NULL,
   orientation CHAR(1) DEFAULT '1',
   beginning_slot DECIMAL(3,1) NOT NULL,
   ending_slot DECIMAL(3,1) NOT NULL,
   device VARCHAR(100) NOT NULL,
   notes TEXT DEFAULT NULL,
   width INT DEFAULT NULL,
   beginning_x INT DEFAULT NULL,
   display CHAR(1) DEFAULT '1',
   updated_by VARCHAR(25) DEFAULT 'Luke Utnowski',
   updated_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   FOREIGN KEY ( rack_id ) REFERENCES racks (rack_id),
   PRIMARY KEY ( device_id )
);

create table connections (
   connection_id INT NOT NULL AUTO_INCREMENT,
   device_input_id INT NOT NULL,
   device_output_id INT NOT NULL,
   cable_type VARCHAR(25) NOT NULL,
   display CHAR(1) DEFAULT '1',
   notes TEXT DEFAULT NULL,
   FOREIGN KEY ( device_input_id ) REFERENCES devices (device_id),
   FOREIGN KEY ( device_output_id ) REFERENCES devices (device_id),
   PRIMARY KEY ( connection_id )
);

and am trying to figure out how I can get the rack_id if I have a connection_id

2

Look at the relationships between the tables, You have the connection_id, so take the table that it exists on and join it to the next table by the appropriate foreign key until you have the value you want (hint, you only need to join two out of the three tables)

3 
SELECT di.rack_id AS rack_in
     , do.rack_id AS rack_out
  FROM connections
INNER
  JOIN devices AS di
    ON di.device_id = connections.device_input_id
INNER
  JOIN devices AS do
    ON do.device_id = connections.device_output_id    
 WHERE connections.id = 37
1 Like
4

I think left joins would be better.

5

so you’re saying the connections table has two foreign keys that reference the devices table, and that they might contain invalid id values?

i disagree completely, good sir or madam

unless, of course, you’re thinking that one or the other might be NULL

which doesn’t make sense – a connection to a non-existing device?

no… just no

1 Like
6

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