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?
Who is Participating?
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
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?
Yes, It should work, or at least the syntex will be very close.
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.