On Sun, Jun 3, 2012 at 4:50 AM, Alexander Reichstadt <l...@mac.com> wrote: > So, I have 4 tables > > pets > persons > companies > pets_reference > > pets have owners, the owner at any point in time is either a persons or a > company, never both at the same time. > > A pet owner can change to persons A, resulting in a record in pets_reference > connecting pet and person with a timestamp, setting refid_companies to zero > and refid_persons to person A's record's id value.
I'd use the SQL NULL value rather than zero here. You can then make use of foreign key constraints easily. > Now, the problem is with displaying a table with pets and only their current > owners. I can't figure out two things. > For one it seems I would need to somehow build a query which uses an if-then > branch to check if companies is zero or persons is zero to ensure to either > reference a persons or a companies record. > The second issue is that I only need the max(ownersince) record, because I > only need the current owner and not past owners. This sounds like a good job for an outer join. Something like this: SELECT * from pets_reference JOIN pets ON (refid_pets = pets.id) LEFT JOIN persons ON (refid_persons = persons.id) LEFT JOIN companies ON (refid_companies = companies.id) That will give you the pet record plus any associated person and/or company data. When refid_companies is NULL, all fields that come from the companies table will be NULL also (that's what the outer join does). The second issue is a little tricky to solve in standard SQL, and there are various techniques that can be used. Here's one involving Postgres's window functions: SELECT refid_pets,first_value(ownersince) over w,first_value(refid_persons) over w,first_value(refid_companies) over w FROM pets_reference WINDOW w AS (partition refid_pets order by ownersince desc) I'm sure there's an easier way to do this, but I'm not an expert with window functions. Hope that helps! Chris Angelico -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
