SQL Problem - Help!

From: Tom Henderson <tom_at_safety.ucl.ac.uk>
Date: Sat, 18 Mar 1995 14:51:25 GMT
Message-ID: <1995Mar18.145125.32776_at_ucl.ac.uk>

Hi,

I'm sure that this is one of those "well you wouldn't start from here  if you wanted to get there" type questions - alternatively I might just be missing the obvious.

The problem:

I have a set of tables representing a university management structure: Faculties, Departments, Sites and Groups. Each table has a primary key used to link to the subordinate table. Thus Departments has a faculty ID column as a foreign key to the faculties table primary key. There is then a table of people and finally a table of posts. The posts table has a foreign key to the people table primary key and a column in which job titles are stored. Then it has a column as a foreign key for each of the management tables. Thus we have columns: Peson_ID, Title, Faculty_ID, Dept._ID, etc.. The integrity constraints allow only one of these managment table reference columns to be not null. A person can thus be appointed to a job anywhere in the management structure. This lot works fine for discovering the post holders for a given management object, my problem now is how do I write a select statement to detail all the jobs held by a person? I need to get not just the primary key and the correct table details but actually data from the management model table.

All suggestions gratefully received!

Tom

+------------------------------------------------------+


|Tom Henderson, Fire Officer, University College London|

|Phone: (+44) 171-380-7845      Fax: (+44) 171-380-7209|

|Email: Tom_at_Safety.ucl.ac.uk or Fire.Officer_at_ucl.ac.uk |
|Post: Safety Office, UCL, Gower Street London WC1E 6BT|

 +------------------------------------------------------+

Received on Sat Mar 18 1995 - 15:51:25 CET