Karsten Hilbert wrote:
Hello all,
maybe some general advice can be had on this:
table test_results
modified_by integer foreign key staff(pk),
intended_reviewer integer foreign key staff(pk),
actual_reviewer integer foreign key staff(pk)
(this table will contain millions of rows)
table staff
pk integer
name text
(this table will contain at most 50 rows)
Now I want to set up a view which aggregates test results
with staff names for all three foreign keys. This would mean
I would either have to
- join test_results to staff three times, once for each
of the foreign keys, this is going to be messy with
tracking table aliases, duplicate column names etc
- write three explicit sub-selects for the columns I want
to denormalize into the view definition
Select testresults.*, Modifer.Name, Intended.name, Actual.name from
testresults
left join (Select pk, name from staff) Modifer
on Modifer.pk = testresults.modified_by
left join (Select pk, name from staff) Intended
on Reviewer.pk = testresults.intended_reviewer
left join (Select pk, name from staff) Actual
on pk = testresults.actual_reviewer
This is what i think you are after. You can do this via nested queries
also for each name
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
