SELECT issue with references to different tables

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 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.

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 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

While you can solve the problem as structured have you considered an "entity" table that is a super-type of both person and company? 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 not null 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 then in 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 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: