Re: SELECT issue with references to different tables

From: Chris Angelico <rosuav(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: SELECT issue with references to different tables
Date: 2012-06-02 19:22:02
Message-ID: CAPTjJmrR1xseiS--od+0Y008ygbNMK_tgf0dqAsS4nnoUtEMGw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Sun, Jun 3, 2012 at 4:50 AM, Alexander Reichstadt <lxr(at)mac(dot)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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message David Johnston 2012-06-02 19:27:12 Re: SELECT issue with references to different tables
Previous Message Alexander Reichstadt 2012-06-02 18:50:53 SELECT issue with references to different tables