From: | David Johnston <polobo(at)yahoo(dot)com> |
---|---|
To: | Alexander Reichstadt <lxr(at)mac(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: SELECT issue with references to different tables |
Date: | 2012-06-02 19:27:12 |
Message-ID: | 18A20594-5E7F-4C81-B43B-85C61454C981@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Jun 2, 2012, at 14:50, Alexander Reichstadt <lxr(at)mac(dot)com> wrote:
> Hi,
>
> I have a query I cannot figure out in postgres or actually in any other way than using the client front end, which I would prefer not to do.
>
> 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.
>
> So, the pets_reference table has the fields:
>
> refid_pets matching table pets, field id
> refid_persons matching table persons, field id
> refid_companies matching table companies, field id
> ownersince which is a timestamp
>
> 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. If the owner changes to some other person B, then another record is added to pets_reference. Or if the owner for that pet changes to a company, then a new record is added with refid_persons being zero and refid_companies being the id value of that companies id field value. So at the end of the day pets_reference results in a history of owners.
>
> 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.
>
> I toyed around with DISTINCT max(ownersince) and GROUP BY, but it only results in errors. I am not the SQL guru, I know my way around so far and am learning, but this is kind of another league and I can't really show any good results I've come up with so far. Please, can someone help?
>
> Thanks
> Alex
>
>
While you can solve the problem as structured have you considered an "entity" table that is a super-type of both person and company? The entity id would then be the foreign key.
For you immediate problem you have to perform a UNION query. The first sub-query will output records where personid is not null and the second sub-query will output records where companyid is not null.
If you are using 8.4 or above after the union you can use a window function (rank) on the ordered ownersince date and then in an outer query filter so that only rank=1 records are kept.
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2012-06-02 20:41:53 | Re: [GENERAL] pg_upgrade from 9.0.7 to 9.1.3: duplicate key pg_authid_oid_index |
Previous Message | Chris Angelico | 2012-06-02 19:22:02 | Re: SELECT issue with references to different tables |