Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> efficiency of SQL query

efficiency of SQL query

From: abracad <abracad_at_nospam.com>
Date: Fri, 09 Apr 2004 20:56:50 GMT
Message-ID: <40770ddc.2929996@news.tiscali.co.uk>


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 Received on Fri Apr 09 2004 - 15:56:50 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US