Multiple joins with "IN" clause

Question

Hi I am trying to get a query going that lists connections between upstream trunks and downstream customer devices.

upstream trunk ports 11/1 12/1---Aggregator device---Customer ports 1/1 1/2

                                  ---Customer ports 2/1 2/2

                                  ---Customer ports 3/1 3/2

                                  --etc

I want to search a customer device and have it return a list of ports customer is connecting to. The customer needs to connect to upstream trunk ports as well as every other customer port. (There are east and west sides to the network) The output table should look something like:

EAST links

Customer1 port 1/1 -> trunk 11/1

Customer1 port1/1 -> Customer2 port 2/1

Customer1 port1/1 -> Customer3 port 3/1

etc

WEST LINKS

Customer1 port 1/2 -> trunk 12/1

Customer1 port1/2 -> Customer2 port 2/2

Customer1 port1/2 -> Customer3 port 3/2

etc

My connections table has upstream ports on source side and customer ports on destination side. The aggregator connects to a core device with core connections on source side and AGG connections on dest side.

I can query each bit to give me what I want, but can't figure out best way to join them together. Should I even try to join them into a single query? Is there a better way? I have also included sample data sql.

My queries are:

//get trunk ports   
$q1 = "select system_name_dest, slot_dest, port_dest, side FROM     connections where system_name_dest IN (select distinct system_name_source     FROM connections where system_name_dest = '01-42C3:N:QLD:7NET:TOWNSVILLE')";

output:

system_name_dest slot_dest port_dest side

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 12 01 E

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 24 01 W

//get all customer ports
$q2 = "select system_name_source, slot_source, port_source, side FROM connections where system_name_source IN (select distinct system_name_source  
  FROM connections where system_name_dest ='01-42C3:N:QLD:7NET:TOWNSVILLE')";

output:

system_name_source slot_source port_source side

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 13 01 E

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 14 01 W

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 01 01 E

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 02 01 W

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 01 02 E

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 02 02 W

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 06 01 E

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 07 01 W

 //get A-side ports
$q3 = "select system_name_source, slot_source, port_source, side  
 FROM connections where system_name_dest='01-42C3:N:QLD:7NET:TOWNSVILLE'";

output

system_name_source slot_source port_source side

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 01 01 E

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 02 01 W

I would like my final table to be something like: Where I have a East list of source port and corresponding connections and also a West list with corresponding connections.

Source Slot Port Destination Slot Port Side

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 01 01 <--> 02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 12 01 E

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 01 01 <--> 02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 13 01 E

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 01 01 <--> 02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 01 02 E

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 01 01 <--> 02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 06 01 E

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 02 01 <--> 02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 24 01 W

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 02 01 <--> 02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 14 01 W

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 02 01 <--> 02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 02 02 W

02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 02 01 <--> 02-46C1:P:QLD:TLS:TOWNSVIL_AGGR 07 01 W

Also included is a subset dump of database table if it helps.

-- phpMyAdmin SQL Dump
-- version 4.2.8
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 18, 2015 at 01:50 AM
-- Server version: 5.6.25-log
-- PHP Version: 5.6.10

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `dvn2`
--

-- --------------------------------------------------------

--
-- Table structure for table `connections`
--

CREATE TABLE IF NOT EXISTS `connections` (
`id` smallint(5) NOT NULL,
  `system_name_source` char(32) COLLATE utf8_unicode_ci NOT NULL,
  `port_type` char(15) COLLATE utf8_unicode_ci NOT NULL,
  `slot_source` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `port_source` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `system_name_dest` char(32) COLLATE utf8_unicode_ci NOT NULL,
  `slot_dest` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `port_dest` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `cable` char(17) COLLATE utf8_unicode_ci NOT NULL,
  `side_name` char(32) COLLATE utf8_unicode_ci NOT NULL,
  `side` char(1) COLLATE utf8_unicode_ci NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB AUTO_INCREMENT=1007 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `connections`
--

INSERT INTO `connections` (`id`, `system_name_source`, `port_type`, `slot_source`, `port_source`, `system_name_dest`, `slot_dest`, `port_dest`, `cable`, `side_name`, `side`, `status`) VALUES
(15, '01-08C3:P:QLD:TLS:CHARLTTE_AGGR', '10G Trunk', '16', '01', '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', '12', '01', 'D 08C3 46C1 DD001', '01-47C5:P:QLD:TLS:WGABBA_AGGR', 'E', 1),
(202, '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', '10G Trunk', '13', '01', '01-11C4:P:QLD:TEL:WILLOWS_STDM', '11', '01', 'D 11C4 46C1 DD001', '', 'E', 1),
(203, '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', '10G Trunk', '14', '01', '01-11C4:P:QLD:TEL:WILLOWS_STDM', '12', '01', 'D 11C4 46C1 DD002', '', 'W', 1),
(204, '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', '1G Trunk', '01', '01', '01-42C3:N:QLD:7NET:TOWNSVILLE', '05', '01', 'D 42C3 46C1 DD001', '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', 'E', 1),
(205, '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', '1G Trunk', '02', '01', '01-42C3:N:QLD:7NET:TOWNSVILLE', '05', '02', 'D 42C3 46C1 DD002', '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', 'W', 1),
(218, '01-47C5:P:QLD:TLS:WGABBA_AGGR', '10G Trunk', '16', '01', '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', '24', '01', 'D 46C1 47C5 DD001', '01-08C3:P:QLD:TLS:CHARLTTE_AGGR', 'W', 1),
(626, '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', '1G Trunk', '01', '02', '01-13C4:P:QLD:SCA:TOWNSVILLE', '11', '01', 'D 13C4 46C1 DD001', '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', 'E', 0),
(627, '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', '1G Trunk', '02', '02', '01-13C4:P:QLD:SCA:TOWNSVILLE', '12', '01', 'D 13C4 46C1 DD002', '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', 'W', 0),
(825, '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', '1G Trunk', '06', '01', '01-24C5:P:QLD:BAUS:MT_STUART', '11', '01', 'D 24C5 46C1 DD001', '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', 'E', 0),
(826, '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', '1G Trunk', '07', '01', '01-24C5:P:QLD:BAUS:MT_STUART', '12', '01', 'D 24C5 46C1 DD002', '02-46C1:P:QLD:TLS:TOWNSVIL_AGGR', 'W', 0);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `connections`
--
ALTER TABLE `connections`
 ADD PRIMARY KEY (`id`), ADD KEY `system_name_source` (`system_name_source`), ADD KEY `system_name_dest` (`system_name_dest`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `connections`
--
ALTER TABLE `connections`
MODIFY `id` smallint(5) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1007;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Kind Regards Nigel

Answer 1

This gives the same set of results as your final table - not in the same order though, you may need to play with that if it is important :

select c1.system_name_source, c1.slot_source, c1.port_source, '<-->', c2.system_name_source, c2.slot_dest, c2.port_dest, c1.side
FROM connections c1
JOIN connections c2 ON (c1.system_name_source=c2.system_name_dest) AND c1.side=c2.side AND c1.id!=c2.id
where c1.system_name_dest='01-42C3:N:QLD:7NET:TOWNSVILLE'
UNION
select c1.system_name_source, c1.slot_source, c1.port_source, '<-->', c2.system_name_source, c2.slot_source, c2.port_source, c1.side
FROM connections c1
JOIN connections c2 ON (c1.system_name_source=c2.system_name_source) AND c1.side=c2.side AND c1.id!=c2.id
where c1.system_name_dest='01-42C3:N:QLD:7NET:TOWNSVILLE'
ORDER BY side