Can I join like this?

I have two tables


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,
   device_input_port VARCHAR(25) NOT NULL,
   device_input_slot VARCHAR(25) NOT NULL,
   device_output_port VARCHAR(25) NOT NULL,
   device_output_slot 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 come up with a select query to output the devicce from the devices table when I have the connection_id.

SELECT * FROM connections WHERE connection_id = 1;

But I was thinking I wanted something like…

SELECT connections.device_input_id,
               connections.device_output_id,
               connections.cable_type,
               connections.device_input_port,
               connections.device_input_slot,
               connections.device_output_port,
               connections.device_output_slot,
               connections.display,
               devices.device
FROM connections
INNER JOIN devices 
ON devices.device_id = connections.device_input_id
WHERE connection_id =1;

ok, so that query works great, and returns the device of the device_input_id


How do I get it to also display the device of the device_output_id?

You give an alias to the join

SELECT connections.device_input_id
     , connections.device_output_id
     , connections.cable_type
     , connections.device_input_port
     , connections.device_input_slot
     , connections.device_output_port
     , connections.device_output_slot
     , connections.display
     , devInput.device
     , devOutput.device
  FROM connections
 INNER JOIN devices devInput ON devInput.device_id = connections.device_input_id
 INNER JOIN devices devOutput ON devOutput.device_id = connections.device_output_id
 WHERE connection_id =1;
1 Like

do you not read the replies to threads you’ve started here?

i already answered this, showing how to join connections to the input and output devices

also, maybe keep everything that’s related in the same thread from now on, mkay?

2 Likes

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