Btukfyl

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.

In the end of query put,

ORDER BY DATA.DataId ASC;

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;

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;