Connecting Two Derived Table

You want to join results from two queries - the derived tables are solution for you.
Look at code below.Yellow highlighted lines are queries that you want to join and green highlighted
lines are inner join conditions.
Lets start with database:

CREATE TABLE IF NOT EXISTS `cconn` (
  `connID` int(11) NOT NULL AUTO_INCREMENT,
  `lineAID` int(11) NOT NULL,
  `lineBID` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`connID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;


INSERT INTO `cconn` (`connID`, `lineAID`, `lineBID`, `name`) VALUES
(1, 1, 2, 'Munich, Germany'),
(2, 3, 4, 'Dortmund, Germany');

CREATE TABLE IF NOT EXISTS `cline` (
  `lineID` int(11) NOT NULL AUTO_INCREMENT,
  `typeID` int(11) NOT NULL,
  `name` varchar(23) NOT NULL,
  PRIMARY KEY (`lineID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `cline` (`lineID`, `typeID`, `name`) VALUES
(1, 1, 'E 53'),
(2, 2, 'E 52'),
(3, 3, 'E 15'),
(4, 4, 'E 28');

CREATE TABLE IF NOT EXISTS `ctype` (
  `typeID` int(11) DEFAULT NULL,
  `name` varchar(11) NOT NULL,
  KEY `typeID` (`typeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `ctype` (`typeID`, `name`) VALUES
(1, 'Highway'),
(2, 'Expressway'),
(3, 'Motorway'),
(4, 'Transit');

and here's the complex query

SELECT first.connID,
       first.lineID,
       first.roadName as 'Road name',
       first.n2 as 'Road Type',
       second.lineID,
       second.roadName as 'Road name',
       second.n2 as 'Road Type',
       second.cityName as 'City Name'
FROM
  (SELECT connID,
          lineID,
          c2.name AS roadName,
          c3.name AS n2
   FROM cconn c1
   INNER JOIN cline c2
   INNER JOIN ctype c3
   WHERE (c1.lineAID = c2.lineID
          OR c1.lineBID = c2.lineID)
     AND c3.typeID = c2.typeID) AS first
INNER JOIN
  (SELECT connID,
          lineID,
          c2.name AS roadName,
          c3.name AS n2,
          c1.name AS cityName
   FROM cconn c1
   INNER JOIN cline c2
   INNER JOIN ctype c3
   WHERE (c1.lineAID = c2.lineID
          OR c1.lineBID = c2.lineID)
     AND c3.typeID = c2.typeID) AS second
WHERE first.connID = second.connID
  AND first.lineID != second.lineID
GROUP BY first.connID

and here's the result

connID	lineID	Road name	Road Type	lineID	Road name	Road Type	City Name
1	2	E 52	Expressway	1	E 53	Highway	Munich, Germany
2	4	E 28	Transit	3	E 15	Motorway	Dortmund, Germany

or you can use alternative solution with multiple aliases for a table:

SELECT connID, c1.name AS 'Road name', t1.name AS 'Road Type', c2.name AS 'Road Name', t2.name AS 'Road Type', c.name AS 'City Name'
FROM `cconn` c
INNER JOIN cline c1 ON c.lineAID = c1.lineID
INNER JOIN cline c2 ON c.lineBID = c2.lineID
INNER JOIN ctype t1 ON c1.typeID = t1.typeID
INNER JOIN ctype t2 ON c2.typeID = t2.typeID

with resultset like this

connID	Road name	Road Type	Road Name	Road Type	City Name
1	E 53	Highway	E 52	Expressway	Munich, Germany
2	E 15	Motorway	E 28	Transit	Dortmund, Germany