Code: Select all
CREATE TABLE `tblasset` (
`AssetID` int(11) NOT NULL,
`AssetName` varchar(50) NOT NULL,
PRIMARY KEY (`AssetID`)
);
INSERT INTO `tblasset` (`AssetID`, `AssetName`) VALUES
(1, 'IPOD'),
(2, 'Laptop'),
(3, 'Sony PS3'),
(4, 'Wooden Cupboard'),
(5, 'Fridge');
CREATE TABLE `tblassetplacement` (
`PlacementID` int(11) NOT NULL,
`LocationID` int(11) NOT NULL,
`AssetID` int(11) NOT NULL,
`PlacementDate` bigint(20) NOT NULL,
`OfficerInChargeID` varchar(50) DEFAULT NULL,
PRIMARY KEY (`PlacementID`)
);
INSERT INTO `tblassetplacement` (`PlacementID`, `LocationID`, `AssetID`, `PlacementDate`, `OfficerInChargeID`) VALUES
(1, 3, 1, 1209628156, 'John'),
(2, 6, 1, 1209800956, 'Susan'),
(3, 3, 3, 1209714556, 'Erik'),
(4, 4, 3, 1210405756, 'Albert'),
(5, 5, 3, 1211096956, 'Fred');
CREATE TABLE `tbllocation` (
`LocationID` int(11) NOT NULL,
`LocationName` varchar(50) NOT NULL,
`ParentID` int(11) DEFAULT NULL,
PRIMARY KEY (`LocationID`),
KEY `ParentID` (`ParentID`)
);
INSERT INTO `tbllocation` (`LocationID`, `LocationName`, `ParentID`) VALUES
(1, 'Building A', NULL),
(2, 'Building B', NULL),
(3, 'Room 1', 1),
(4, 'Room 2', 1),
(5, 'Partition 1', 4),
(6, 'Room 1', 2);
Location Tree View
---
.___ Building A
. .___ Room 1
. .___ Room 2
. . .___ Partition 1
.
.___ Building B
. .___ Room 1
on tblAssetPlacement, the column PlacementDate is a unix timestamp stored as bigint.
the higher of its value means it is the latest placement.
so i had created a sql to pull the data like this:
Code: Select all
SELECT
tblasset.AssetName,
tblassetplacement.OfficerInChargeID,
tblassetplacement.Locationid,
tbllocation.LocationName
FROM
tblasset,tblassetplacement, tbllocation,
(select tblassetplacement.AssetID,tblassetplacement.LocationID,MAX(tblassetplacement.PlacementDate) AS LatestDate from tblassetplacement group by tblassetplacement.AssetID) temptbl
where
tblasset.AssetID = tblassetplacement.AssetID
and tblassetplacement.PlacementDate = temptbl.LatestDate
and tblassetplacement.AssetID = temptbl.AssetID
and tbllocation.LocationID=tblassetplacement.LocationID
;
---
AssetName OfficerInChargeID LocationID LocationName
IPODs Susan 6 Room 1
Sony PS3 Fred 5 Partition 1
i would need to have a column ParentLocationName that directly shows the name of the parent's location as below:
---
AssetName OfficerInChargeID LocationID LocationName ParentLocationName
IPOD Susan 6 Room 1 Building B
Sony PS3 Fred 5 Partition 1 Building A
is there a way to pull the parent's location name in one sql syntax?