Re: performance advice needed: join vs explicit subselect

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
>