location database - cant figure out a query for this.

Posted on 2005-05-10
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
    LVL 14

    Accepted Solution

    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

    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

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    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.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now