ORDER BY a subqueries which has JOIN, LEFT JOIN and UNION

Question

My question is: How do I order the subquery by PositionAssetId and then follow by its related PhysicalAssetId based on table TrxAssetPool?

I need LEFT JOIN because not all Position and Physical were linked together. Some of Position/Physical were standalone. A Physical might exist in PhysicalAsset and TrxPhysicalAsset but not exist in TrxAssetPool because it was not linked to any Position; and vice versa. These data also needed to be displayed.

CREATE TABLE `PositionAssets` (
  `Id` int(5) unsigned NOT NULL,
  `Code` varchar(50) NOT NULL,
  `Desc` varchar(200) NOT NULL,
  PRIMARY KEY (`Id`)
);
CREATE TABLE `PhysicalAssets` (
  `Id` int(5) unsigned NOT NULL,
  `Code` varchar(50) NOT NULL,
  `Desc` varchar(200) NOT NULL,
  PRIMARY KEY (`Id`)
);
CREATE TABLE `TrxPositionAssets` (
  `Id` int(5) unsigned NOT NULL,
  `MaintTrxId` int(5) unsigned NOT NULL,
  `PositionAssetId` int(5) NOT NULL,
  PRIMARY KEY (`Id`,`MaintTrxId`)
);
CREATE TABLE `TrxPhysicalAssets` (
  `Id` int(5) unsigned NOT NULL,
  `MaintTrxId` int(5) unsigned NOT NULL,
  `PhysicalAssetId` int(5) NOT NULL,
  PRIMARY KEY (`Id`,`MaintTrxId`)
);
CREATE TABLE `TrxAssetPool` (
  `Id` int(5) unsigned NOT NULL,
  `MaintTrxId` int(5) NOT NULL,
  `PositionAssetId` int(5) NOT NULL,
  `PhysicalAssetId` int(5) NOT NULL,
  PRIMARY KEY (`Id`)
);
INSERT INTO `PositionAssets` (`Id`, `Code`, `Desc`) VALUES
  ('1', 'PositionC', 'Air conditioner'),
  ('2', 'PositionB', 'Laptop'),
  ('3', 'PositionA', 'Mobile Phone')
  ;
INSERT INTO `PhysicalAssets` (`Id`, `Code`, `Desc`) VALUES
  ('1', 'PhysicalD', 'Dunlop Car Tyre'),
  ('2', 'PhysicalA1', 'Samsung'),
  ('3', 'PhysicalB2', 'Acer'),
  ('4', 'PhysicalB1', 'Lenovo')
  ;
INSERT INTO `TrxPositionAssets` (`Id`, `MaintTrxId`, `PositionAssetId`) VALUES
  ('1', '1', '2'),
  ('2', '1', '3'),
  ('3', '1', '1')
  ;
INSERT INTO `TrxPhysicalAssets` (`Id`, `MaintTrxId`, `PhysicalAssetId`) VALUES
  ('1', '1', '2'),
  ('2', '1', '3'),
  ('3', '1', '1'),
  ('4', '1', '4')
  ;
INSERT INTO `TrxAssetPool` (`Id`,`MaintTrxId`,`PositionAssetId`,`PhysicalAssetId`) VALUES
  ('1', '1', '3', '2'),
  ('2', '1', '2', '4'),
  ('3', '1', '2', '3')
  ; 

SELECT DataType, DataCode, DataDesc 
FROM ( 
    SELECT 'Position' AS DataType, pos.Code AS DataCode, pos.Desc AS DataDesc
        FROM TrxPositionAssets trxpos
        JOIN PositionAssets pos ON pos.Id = trxpos.PositionAssetId
        LEFT JOIN TrxAssetPool trxpool ON (trxpool.PositionAssetId = trxpos.PositionAssetId and trxpool.MaintTrxId = trxpos.MaintTrxId)
        WHERE trxpos.MaintTrxId = 1
    UNION
    SELECT 'Physical' AS DataType, phy.Code AS DataCode, phy.Desc  AS DataDesc
        FROM TrxPhysicalAssets trxphy
        JOIN PhysicalAssets phy ON phy.Id = trxphy.PhysicalAssetId
        LEFT JOIN TrxAssetPool trxpool ON (trxpool.PhysicalAssetId = trxphy.PhysicalAssetId and trxpool.MaintTrxId = trxphy.MaintTrxId)
        WHERE trxphy.MaintTrxId = 1  
) DataPool

Sample at sqlfiddle.com

Current result:

DataType    DataCode    DataDesc

Position    PositionA   Mobile Phone
Position    PositionB   Laptop
Position    PositionC   Air conditioner
Physical    PhysicalA1  Samsung
Physical    PhysicalB1  Lenovo
Physical    PhysicalB2  Acer
Physical    PhysicalD   Dunlop Car Tyre

Expected Result:

DataType    DataCode    DataDesc

Position    PositionA   Mobile Phone
Physical    PhysicalA1  Samsung
Position    PositionB   Laptop
Physical    PhysicalB1  Lenovo
Physical    PhysicalB2  Acer
Position    PositionC   Air conditioner
Physical    PhysicalD   Dunlop Car Tyre

Air conditioner is not related to any Physical. Dunlop Car Tyre is not related to any Position.

Answer 1

In the end of query put,

ORDER BY DATA.DataId ASC;

Answer 2

You need to select the information you want in the subquery. Also, the LEFT JOINs are not necessary, because they are undone by the WHERE and you probably want UNION ALL:

SELECT Data.[DataId], Data.[TrxnDataId], Data.[Type]
FROM ((SELECT pa.[Id] AS DataId, tpa.[Id] AS TrxnDataId, 'Position' AS Type,
              tap.PositionAssetId, 1 as ord
       FROM {TrxPositionAssets} tpa JOIN
            {PositionAssets} pa
            ON pa.[Id] = tpa.[PositionAssetId] JOIN
            TrxAssetPool} tap
            ON tap.[PositionAssetId] = pa.[Id] AND tap.[TrxId] = tpa.[TrxId])
         WHERE tpa.[TrxId] = @TrxId 
      ) UNION ALL
      (SELECT pa.[Id] AS DataId, tpa.[Id] AS TrxnDataId, 'Physical' AS Type,
              tap.PositionAssetId, 2 as ord
       FROM {TrxPhysicalAssets} tpa JOIN
            {PhysicalAssets} pa
            ON pa.[Id] = tpa.[PhysicalAssetId] JOIN
            {TrxAssetPool} tap
            ON tap.[PhysicalAssetId] = pa.[Id] AND tap.[TrxId] = tpa.[TrxId]
       WHERE tpa.[TrxId] = @TrxId
      )
     ) data 
ORDER BY PositionAssetId, ord, dataId;

Answer 3

To solve your problem you have to simplify it and solve it step by step, it will be easier to find a solution.

E.g. Simply join two table

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;