i don't get values from 4 tables in right order

Haroldd · August 1st, 2003, 04:32 AM

hi,

i have 4 tables which i need to create a list of values. it's easier for the user if they are alphabetisized so he can find them easier...but i can't figure out exactly how to do that.

my 4 tables:
LOCATION ([u]locationId</u>, name)
DEPARTMENT ([u]departmentId</u>, locationId, name)
SUBDEPARTMENT ([u]subdepartmentId</u>, departmentid, name)
COST ([u]costId</u>, locationId, departmentId, subdepartmentId, name)

a location has 1 to many costs
a department has 1 to many costs
a department has 1 to many subdepartments

What i need is a list in this order:
===========================
location
    department
        subdepartment
            cost
===========================

somehow i can only get this list for one location and not for all of them

if some sql whizzkid could write a statement for this problem i would be very grateful :D

thanks in advance,

Harold

Jeff Mason · August 10th, 2003, 08:06 AM

You aren't very clear on what your desired output is, but how about:

Code:

SELECT LOCATION.name as LocName, DEPARTMENT.name as DeptName, SUBDEPARTMENT.name as SubName, COST.name as CostName
FROM COST
    INNER JOIN LOCATION ON COST.LocationId=LOCATION.LocationId
    INNER JOIN DEPARTMENT ON COST.departmentId=DEPARTMENT.departmentId
    INNER JOIN SUBDEPARTMENT ON COST.subdepartmentId=SUBDEPARTMENT.subdepartmentId
ORDER BY LocName, DeptName, SubName, CostName;

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Deepesh_Jain · September 19th, 2003, 02:48 AM

SELECT LOCATION.name as LocName, DEPARTMENT.name as DeptName, SUBDEPARTMENT.name as SubName, COST.name as CostName
FROM LOCATION, DEPARTMENT, SUBDEPARTMENT, COST
WHERE
COST.LocationId=LOCATION.LocationId AND COST.departmentId=DEPARTMENT.departmentId AND
COST.subdepartmentId=SUBDEPARTMENT.subdepartmentId
ORDER BY LocName, DeptName, SubName, CostName;

Deepesh Jain
VB,VBA & .NET Specialist
Wiley Support Team

Jeff Mason · September 19th, 2003, 06:49 AM

Quote:

quote:Originally posted by Deepesh_Jain
SELECT LOCATION.name as LocName, DEPARTMENT.name as DeptName, SUBDEPARTMENT.name as SubName, COST.name as CostName
FROM LOCATION, DEPARTMENT, SUBDEPARTMENT, COST
WHERE
COST.LocationId=LOCATION.LocationId AND COST.departmentId=DEPARTMENT.departmentId AND
COST.subdepartmentId=SUBDEPARTMENT.subdepartmentId
ORDER BY LocName, DeptName, SubName, CostName;

Deepesh Jain
VB,VBA & .NET Specialist
Wiley Support Team

This is exactly equivalent to the query I posted above, but it uses the deprecated "old style join" notation. I don't recommend that people use this style any longer. It's OK for INNER JOINs and the two forms of query will result in identical execution plans, but ambiguities can result when using the "old style" for OUTER JOINs and the associated *= operator in the WHERE clause.

This is why the ANSI SQL 92 standard refers to the construction as "old style join" syntax, and introduces the new syntax.

Besides, YMMV, but I think the newer style is easier to read, and refrains from "cluttering up" the WHERE clause leaving it for true row selection operations.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com