Обсуждение: SELECT issue with references to different tables
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
On Sun, Jun 3, 2012 at 4:50 AM, Alexander Reichstadt <lxr@mac.com> wrote: > 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. > > 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. I'd use the SQL NULL value rather than zero here. You can then make use of foreign key constraints easily. > 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. This sounds like a good job for an outer join. Something like this: SELECT * from pets_reference JOIN pets ON (refid_pets = pets.id) LEFT JOIN persons ON (refid_persons = persons.id) LEFT JOIN companies ON (refid_companies = companies.id) That will give you the pet record plus any associated person and/or company data. When refid_companies is NULL, all fields that come from the companies table will be NULL also (that's what the outer join does). The second issue is a little tricky to solve in standard SQL, and there are various techniques that can be used. Here's one involving Postgres's window functions: SELECT refid_pets,first_value(ownersince) over w,first_value(refid_persons) over w,first_value(refid_companies) over w FROM pets_reference WINDOW w AS (partition refid_pets order by ownersince desc) I'm sure there's an easier way to do this, but I'm not an expert with window functions. Hope that helps! Chris Angelico
On Jun 2, 2012, at 14:50, Alexander Reichstadt <lxr@mac.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 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 knowmy way around so far and am learning, but this is kind of another league and I can't really show any good results I'vecome 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 andcompany? 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 notnull 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 thenin an outer query filter so that only rank=1 records are kept. David J.
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@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 >
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 experienceswith it and the overhead of maintaining an additional table for a current status alongside a table for trackinga historical buildup is negligable and works pretty well. In this other case however I also need to do some calculationsand derive amounts of items in stock, prices and so forth. It was rather a gut feeling to have this dual-tablething in said use case. But in this current scenario it seemed I simply lacked SQL practice to achieve this. Yourone-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@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