Re: abuse of inheritance?

On Wed, 2002-07-10 at 15:46, Alex Rice wrote:
> Should I be using table inheritance in this scenario?
>
> Table "contact" has name, address, phone, email, etc. columns.
> Table "staff" inherits from "contact because it has many columns in
> common with "contact".
>
> But I want to query staff and get a list of staff-- and omit non-staff
> contacts. The ONLY directive only works down the inheritance tree, so I
> would have to do (something like) this to get a list of staff only:
>
> SELECT staff.whatever
> FROM staff, pg_class
> WHERE staff.tableoid = pg_class.oid AND pg_class.relname = 'staff'
>
> This strikes me as kinda funny and maybe I should not have inherited
> staff from contact to begin with? Not really up to speed on the whole
> object-relational concept yet. :-)

I don't understand what you're wanting here - is there a misprint
above? What you have done seems to be fine.

Assuming this structure:

contacts
|
staff

If you want a list of staff:

SELECT * FROM staff

If you want a list of non-staff:

SELECT * FROM ONLY contacts

If you want everyone:

SELECT * FROM contacts

If the structure is

contacts
|
+-------------------+-----------------+
| | |
staff salesmen bureaucrats

getting a list of non-staff needs a union:

SELECT col1, col2 FROM salesmen
UNION
SELECT col1, col2 FROM bureaucrats

or possibly an EXCEPT, if there are a lot of inherited tables:

SELECT col1, col2 FROM contacts
EXCEPT
SELECT col1, col2 FROM staff