SELECT issue with references to different tables
От | Alexander Reichstadt |
---|---|
Тема | SELECT issue with references to different tables |
Дата | |
Msg-id | D91CC34A-7FBC-4B73-9749-7B7BE77D3452@mac.com обсуждение исходный текст |
Ответы |
Re: SELECT issue with references to different tables
Re: SELECT issue with references to different tables Re: SELECT issue with references to different tables |
Список | pgsql-general |
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 wouldprefer 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 someother person B, then another record is added to pets_reference. Or if the owner for that pet changes to a company, thena new record is added with refid_persons being zero and refid_companies being the id value of that companies id fieldvalue. 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 personsis 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 myway around so far and am learning, but this is kind of another league and I can't really show any good results I've comeup with so far. Please, can someone help? Thanks Alex
В списке pgsql-general по дате отправления: