Re: SELECT issue with references to different tables

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
>