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