SQL query problem for Reporting [SOLVED]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
buellart
Forum Newbie
Posts: 3
Joined: Sun Jun 01, 2003 10:58 pm
Location: equine
Contact:

SQL query problem for Reporting [SOLVED]

Post by buellart »

hi guys, im having a sql problem for reporting here. here is the table

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);
 
basically the location looks like this

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
;
 
this will output is this:
---
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?
Last edited by buellart on Sat May 31, 2008 8:51 am, edited 1 time in total.
buellart
Forum Newbie
Posts: 3
Joined: Sun Jun 01, 2003 10:58 pm
Location: equine
Contact:

Re: SQL query problem for Reporting

Post by buellart »

hi folks, got this problem solved, here is the solution:

Code: Select all

 
SELECT 
tblasset.AssetName, 
tblassetplacement.OfficerInChargeID, 
tblassetplacement.Locationid, 
tbllocation.LocationName,
COALESCE(GrGrGrandParentL.LocationName 
,GrGrandParentL.LocationName 
,GrandParentL.LocationName 
,ParentL.LocationName ) AS TopLocationName 
 
FROM 
tblasset,tblassetplacement, tbllocation, 
(SELECT tblassetplacement.AssetID,tblassetplacement.LocationID,MAX(tblassetplacement.PlacementDate) AS LatestDate 
FROM tblassetplacement GROUP BY tblassetplacement.AssetID) temptbl 
 
INNER JOIN tblasset AS A 
ON A.AssetID = temptbl.AssetID 
 
INNER JOIN tbllocation AS L
ON L.LocationID = temptbl.LocationID
 
LEFT OUTER JOIN tbllocation AS ParentL
ON ParentL.LocationID = L.ParentID
 
LEFT OUTER JOIN tbllocation AS GrandParentL
ON GrandParentL.LocationID = ParentL.ParentID
 
LEFT OUTER JOIN tbllocation AS GrGrandParentL
ON GrGrandParentL.LocationID = GrandParentL.ParentID
 
LEFT OUTER JOIN tbllocation AS GrGrGrandParentL
ON GrGrGrandParentL.LocationID = GrGrandParentL.ParentID
 
WHERE
tblasset.AssetID = tblassetplacement.AssetID 
AND tblassetplacement.PlacementDate = temptbl.LatestDate 
AND tblassetplacement.AssetID = temptbl.AssetID 
AND tbllocation.LocationID=tblassetplacement.LocationID 
;
re-check the result using this query

Code: Select all

 
SELECT A.AssetName
,AP.OfficerInChargeID
,AP.PlacementDate
,L.Locationid
,L.LocationName
,COALESCE(GrGrGrandParentL.LocationName 
,GrGrandParentL.LocationName 
,GrandParentL.LocationName 
,ParentL.LocationName ) AS TopLocationName
 
FROM ( SELECT AssetID
,LocationID
,MAX(PlacementDate) AS LatestDate 
FROM tblassetplacement 
GROUP BY AssetID,LocationID ) AS temptbl
 
INNER JOIN tblassetplacement AS AP
ON AP.AssetID = temptbl.AssetID
AND AP.LocationID = temptbl.LocationID
AND AP.PlacementDate = temptbl.LatestDate 
 
INNER JOIN tblasset AS A
ON A.AssetID = temptbl.AssetID 
 
INNER JOIN tbllocation AS L
ON L.LocationID = temptbl.LocationID
 
LEFT OUTER JOIN tbllocation AS ParentL
ON ParentL.LocationID = L.ParentID
 
LEFT OUTER JOIN tbllocation AS GrandParentL
ON GrandParentL.LocationID = ParentL.ParentID
 
LEFT OUTER JOIN tbllocation AS GrGrandParentL
ON GrGrandParentL.LocationID = GrandParentL.ParentID
 
LEFT OUTER JOIN tbllocation AS GrGrGrandParentL
ON GrGrGrandParentL.LocationID = GrGrandParentL.ParentID
 
ORDER BY assetname,placementdate
;
credit to all from:
Post Reply