location database - cant figure out a query for this.

I have access to a MSSQL database that i do not administer, that i need to make a query for. Its got 2 tables: a list of devices and a list of locations. Like this:
---devices table----------------
deviceId     LocationParentId
*******    *************
device1       1
device2       3

---locations table--------------
locationId   locationParentId    LocationTypeId   LocationDescription
********   *************   ************   ****************
1                2                         room                 room 1
2                4                         building             building 123
4                                           site                   Site xyz
3                3                         room                 room 3


Each device has a parent location id which could be for example a building or room. This then references a locationId in the locations table, which in turn references a parent location in the same table. The top most location id is a "site" with a LocationTypeId of 1.

I need a query that lists devices with a full "address" string, that only lists devices in a particular site (a particular locationId). To make it worse devices dont always have the same number of parent locations, but they only ever have a maximum of 6 parent locations.

an example of what i need:

device       address string
*****       ***********
device1      room1, building 123, site xyz


Is there a way to do this using only SQL?
godmonkeeAsked:
Who is Participating?
 
adwisemanCommented:
try something like this


Select d1.deviceId, ISNULL(L6.LocationDescription + ', ', '') +
                    ISNULL(L5.LocationDescription + ', ', '') +
                    ISNULL(L4.LocationDescription + ', ', '') +
                    ISNULL(L3.LocationDescription + ', ', '') +
                    ISNULL(L2.LocationDescription + ', ', '') +
                    ISNULL(L1.LocationDescription, '') as [Address String]
FROM Device d1
LEFT OUTER JOIN Location L1 ON L1.locationId = D1.LocationParentId
LEFT OUTER JOIN Location L2 ON L2.locationId = L1.LocationParentId
LEFT OUTER JOIN Location L3 ON L3.locationId = L2.LocationParentId
LEFT OUTER JOIN Location L4 ON L4.locationId = L3.LocationParentId
LEFT OUTER JOIN Location L5 ON L5.locationId = L4.LocationParentId
LEFT OUTER JOIN Location L6 ON L6.locationId = L5.LocationParentId
0
 
godmonkeeAuthor Commented:
Thanks adwiseman, I haven't had the chance to try this yet because im told there are "technical" problems with the sql server in question. I was trying to model this in access just now, but i couldn't get it to work. I assume the jet engine sql syntax is different or something. Can you use this method in access too?
0
 
adwisemanCommented:
Yes, It should work, or at least the syntex will be very close.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.