Search Postgresql Archives

Re: performance advice needed: join vs explicit subselect

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



typo sorry

justin wrote:
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 Inteded.pk  = testresults.intended_reviewer
   left join (Select pk, name  from staff) Actual
       on Actual.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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux