efficiency of SQL query
Date: Fri, 09 Apr 2004 20:56:50 GMT
I have the following 2 tables:
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 - 22:56:50 CEST