efficiency of query

[abracad]

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>=location.lft And
locLft<=location.rgt)
AND locLft>=lft AND locLft<=rgt

But is this the most efficient way of doing so?

Thanks

[Hugo Kornelis]

On Thu, 08 Apr 2004 21:08:16 GMT, abracad wrote:

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>=location.lft And
locLft<=location.rgt)
AND locLft>=lft AND locLft<=rgt

But is this the most efficient way of doing so?

Thanks

Maybe I am missing something, but it seems as if this would also do
the trick:

SELECT name, location
FROM staff
INNER JOIN location
ON locLft BETWEEN lft AND rgt

(Since you did not provide DDL and INSERT statements to recreate and
populate your tables, I can't test this)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)