Re: SQL Problem - Help!

Tom Henderson (tom_at_safety.ucl.ac.uk) wrote:
: 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!

That's about as clear as mud! But I think you're saying that you should have a Managed_Objects table with a type/class attribute with a domain of ('F','D','S','G'). You may or may not need separate tables for each class to allow you to store different attributes for different classes. Your jobs table would then have a 2-column composite Foreign Key into Managed_Objects and a Foreign Key into Persons. This is then (I believe) the management model table which you refer to but do not describe.

If you needed class specific attributes in your report, then you could outer-join through Managed_Objects with a four-way UNION. This technique could be used with your existing posts table structure, but is probably not the best way to go.

: 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|
: +------------------------------------------------------+

--
graeme
--
Disclaimer:	The author's opinions are his own, and not necessarily
		those of Pyramid Technology Ltd. or Pyramid Technology Inc.
---------------------------------------------------------------------------
      -m------- Graeme Sargent                 Voice: +44 (0)252 373035
    ---mmm----- Senior Database Consultant     Fax  : +44 (0)252 373135
  -----mmmmm--- Pyramid Technology Ltd.        Telex: Tell who???
-------mmmmmmm- Farnborough, Hants  GU14 7PL   Email: graeme_at_pyra.co.uk
---------------------------------------------------------------------------
    We have the technology.  The tricky bit is learning how to use it.