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 <l...@mac.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@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
