Complex subquery?

[pser]

I've been asked to create a query from a table in a database. I haven't design the database and wouldn't create it like this. Anyway i'm a little stuck and could need some help.

Table contain several fields but only three of them is important. If i do this: select * from project

i get this result:

iID--------Name-------iParentID
1----------Root--------0
2----------Africa-------1
3----------Project1----2
4----------SubProj1---3
5----------SubProj2---3
6----------Project2----2
7----------Europe-----1
8----------Project1----7
9----------SubProj1---8

As you can see iParent show which the field is related to as this

root
--Africa
----Project1
------SubProj1
------SubProj2
----Project2
--Europe
----Project1

Is it possible the create a query showing a list of the fields related to each other, maybe concatenate it together like this:

Africa - Project1 - Subproj1
Africa - Project1 - Subproj2
Africa - Project2
Europe - Project1

I'm so frustrate over the designer of this database cause it's making it hard to create query's.

Thanks
Paal

 

[Jon K]

You can do it with two self-joins in a query (as shown in the attached DB):-

SELECT Country.Name AS Country, Project.Name AS Project, SubProj.Name AS SubProj
FROM (tblProject AS Project INNER JOIN tblProject AS Country ON Project.iParentID = Country.iID) LEFT JOIN tblProject AS SubProj ON Project.iID = SubProj.iParentID
WHERE (((Country.iParentID)=1));


(To open the DB in Access 2000 or 2002, just choose Convert and save as a new name when it is opened for the first time.)

 

[pser]

Nice work, but then you need to now every name of project, subproject, which i don't know. Project, subproject was just two example.
I could get all project subproj and put it into the query, but it would then be huge and the need of query update every time a new project is added is not what i was looking for.

Paal

 

[FoFa]

If it truly is only 3 deep, you could get by with a Self joined query, but I would guess this is better suited to a recursive VBA function. You might search for Inventory, or maybe even better bill of materials and you may find a subroutine that can be modified to suit your needs.

 

[pser]

I've checked the particular table in deep and there 4 level depth. I guess that would make it even harder.

Paal

 

[Pat Hartman]

To create a query you need to join the table to itself x times with x being the lowest level of nesting. Your join should look similar to the following:

tblA ---> tblA_1 ---> tblA_2 ---> tblA_3

Notice how the builder appends a suffex as each new instance of the table is added to the QBE grid. The join goes from the ParentID of one table to the ID of the next. When you select columns from the tables, it will be easier on you if you give each selected field a unique name. Include the level if you have nothing else meaningful.

A recursive function can be written to not care how deep the structure is. A query needs to hard-code the links to the maximum depth so is useful only when the depth is less than around ? levels. I've gone as deep as 6. I don't know where you will run into trouble.

 

[pser]

Nice tip, i'll try to look into that when i get back to work on monday. I do not have to much experience with self join queries. I've tried to search around a little bit but haven't found any site with good example..

Any tip of sites with example would be appreciated.

Thanks

Paal

 

[FoFa]

When a table holds like data, just more than 1 level as you have, self-joined queries come into play. Think of an Human Resource database with managers, supervisors, and workers. A worker has a supervisor, a supervisor has a manager (stop there for this example). But all these people are in the Employees table. So what happens is you self join the table to pull the data.

EmpTable
EmpID
EmpSpvrID
EmpName

So you can join the table to itself on EmpSpvrID = EmpID to pull the supervisor for an employee.

So:
SELECT E1.EmpID, E1.EmpName, E2.EmpName
FROM EmpTable as E1
LEFT JOIN EmpTable as E2 on E1.EmpSprvID = E2.EmpID

would list the employees and their supervisor (if they have one).
You can logically self join to the Supervisor to pull 2 levels of supervisors such as:
SELECT E1.EmpID, E1.EmpName, E2.EmpName, E3.EmpName
FROM EmpTable as E1
LEFT JOIN EmpTable as E2 on E1.EmpSprvID = E2.EmpID
LEFT JOIN EmpTable as E3 on E2.EmpSprvID = E3.EmpID

Now you have Employee, His supervisor and his supervisors supervisor.
Clear as mud?