From: | Alexander Reichstadt <lxr(at)mac(dot)com> |
---|---|
To: | Misa Simic <misa(dot)simic(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: SELECT issue with references to different tables |
Date: | 2012-06-03 21:23:16 |
Message-ID: | A2BF967F-F509-4CD4-9EBE-A50ABA2D3330@mac.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi Misa,
I have such a construct with kind of an auxiliary table that is only there to keep the current status. I made good experiences with it and the overhead of maintaining an additional table for a current status alongside a table for tracking a historical buildup is negligable and works pretty well. In this other case however I also need to do some calculations and derive amounts of items in stock, prices and so forth. It was rather a gut feeling to have this dual-table thing in said use case. But in this current scenario it seemed I simply lacked SQL practice to achieve this. Your one-shot query works perfectly, your speaking naming convention is self-explaanatory in its transparence.
Thank you, and also thank you to all others who responded.
Alex
Am 03.06.2012 um 12:00 schrieb Misa Simic:
> Hi Alex,
>
> I think would be better to reorganise model to awoid NULLs.... i.e. to
> includ new table:
>
> owners
> -owner_id
> -owner_name
> -ownertype (person/comapny)
>
> and have yours person_details table... and comapny_details_table...
> related 1:1 to owner_id
>
> However, solution for your way I think would be:
>
> SELECT * from pets_reference main JOIN pets ON (refid_pets = pets.id) LEFT
> JOIN persons ON (refid_persons = persons.id) LEFT JOIN companies ON
> (refid_companies = companies.id)
> WHERE ownersince = (SELECT MAX(ownersince) FROM pets_reference child
> WHERE child.ref_petid = main.ref_petid)
>
> Kind Regards,
>
> Misa
>
>
> 2012/6/2, Alexander Reichstadt <lxr(at)mac(dot)com>:
>> 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
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Reichstadt | 2012-06-03 22:39:22 | [postgres no lnger launches |
Previous Message | Matthias | 2012-06-03 20:42:00 | RLS & SEpgsql |