Re: sequential joins

Josh,
the sub-select statement I was using were not for filtering data (in which
case you can move filtering condition in ON part of the join), but rather
renaming the column 'username' with the name I need in the current join
statement. In this case, columns with new names ('artist', 'designer',
'programmer') would be appended to the overall query result and can be
selected by top-level select statement.
This looks like a very clumsy way to do what I need.
Any ideas?
thanks,

Oleg

Josh Berkus wrote:

> Oleg,
>
> > I wonder if there is a better way to do this. Maybe using CASE WHEN
> > THEN
> > ELSE END clause to avoid multiple scans?
> > thanks,
>
> No, not really. PostgreSQL is pretty good about detecting multiple
> references to the same table in subselects and optimizing your query
> appropriately. On occassion, I've had to do this with the same table
> sub-selected 50 seperate times and Postgres handles it OK.
>
> Sometimes you can use a straight LEFT OUTER JOIN instead of a
> subselect. This depends entirely on whether you are planning on doing
> any GROUPing or totals on the main query. If NOT, then:
>
> SELECT tablea.f1, tablea.f2, alias1.f3, alias2.f3
> FROM tablea
> LEFT OUTER JOIN (SELECT f2, f3
> FROM tableb WHERE f4 = "1") alias1
> ON tablea.f1 = alias1.f2
> LEFT OUTER JOIN (SELECT f2, f3
> FROM tableb WHERE f4 = "2") alias2
> ON tablea.f1 = alias2.f2;
>
> Is equivalent to:
>
> SELECT tablea.f1, tablea.f2, alias1.f3, alias2.f3
> FROM tablea
> LEFT OUTER JOIN tableb AS alias1
> ON (tablea.f1 = alias1.f2 AND alias1.f4 = "1")
> LEFT OUTER JOIN tableb AS alias2
> ON (tablea.f1 = alias2.f2 AND alias2.f4 = "2") ;
>
> And the second should run a bit faster.
>
> (FYI: MS SQL Server 7.0 does *not* optimize for multiple subselects on
> the same table. I recently found this out the hard way, and crashed
> an MS SQL Server despite 1gb of memory in the machine. The same query
> does OK in Postgres on less hardware)
>
> -Josh Berkus
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)