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
