I have the following 2 tables:
location:
place lft rgt
-------------------
Europe 0 99
England 1 10
France 11 20
Italy 21 30
Asia 100 199
London 12 12
staff:
name locLft
--------------
Edwards 0
Smith 1
Leveil 11
Rossi 21
Lee 12
Chan 100
location uses the Celko hierarchy model.
I wish to retrieve for a location the names of all staff within it and
the hierarchy of place associated with that member of staff, eg a
query for Europe should return all staff in Europe, and for Lee I wish
to return Lee-London, Lee-England, Lee-Europe etc.
I can achieve this using a subquery, ie
SELECT name, place
FROM staff, location
WHERE name IN (SELECT name
FROM staff, location
WHERE place='Europe' And locLft>=locatio n.lft And
locLft<=locatio n.rgt)
AND locLft>=lft AND locLft<=rgt
But is this the most efficient way of doing so?
Thanks
location:
place lft rgt
-------------------
Europe 0 99
England 1 10
France 11 20
Italy 21 30
Asia 100 199
London 12 12
staff:
name locLft
--------------
Edwards 0
Smith 1
Leveil 11
Rossi 21
Lee 12
Chan 100
location uses the Celko hierarchy model.
I wish to retrieve for a location the names of all staff within it and
the hierarchy of place associated with that member of staff, eg a
query for Europe should return all staff in Europe, and for Lee I wish
to return Lee-London, Lee-England, Lee-Europe etc.
I can achieve this using a subquery, ie
SELECT name, place
FROM staff, location
WHERE name IN (SELECT name
FROM staff, location
WHERE place='Europe' And locLft>=locatio n.lft And
locLft<=locatio n.rgt)
AND locLft>=lft AND locLft<=rgt
But is this the most efficient way of doing so?
Thanks