efficiency of query

I have the following 2 tables:

location:
placelftrgt
------
Europe099
England110
France1120
Italy2130
Asia100199
London1212

staff:
namelocLft
-----
Edwards0
Smith1
Leveil11
Rossi21
Lee12
Chan100

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?

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

>I have the following 2 tables:
>location:
>placelftrgt
>------
>Europe099
>England110
>France1120
>Italy2130
>Asia100199
>London1212
>staff:
>namelocLft
>-----
>Edwards0
>Smith1
>Leveil11
>Rossi21
>Lee12
>Chan100
>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)