[Last Call] Learn how to a build a cloud-first strategyRegister Now


location database - cant figure out a query for this.

Posted on 2005-05-10
Medium Priority
Last Modified: 2010-03-19
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?
Question by:godmonkee
  • 2
LVL 14

Accepted Solution

adwiseman earned 1050 total points
ID: 13967793
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

Author Comment

ID: 14005480
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?
LVL 14

Expert Comment

ID: 14009676
Yes, It should work, or at least the syntex will be very close.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question