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