Received: from mail1.waterford.org (mail1.waterford.org [205.124.117.6]) by postgresql.org (Postfix) with ESMTP id 0866D47593E for ; Fri, 1 Mar 2002 18:23:26 -0500 (EST) Received: from waterford.org ([205.124.138.93]) by mail1.waterford.org (Netscape Messaging Server 3.56) with ESMTP id 251 for ; Fri, 1 Mar 2002 16:11:27 -0700 Message-ID: <3C801303.8DF6A0D1@waterford.org> Date: Fri, 01 Mar 2002 16:47:15 -0700 From: Oleg Lebedev X-Mailer: Mozilla 4.72 [en] (Windows NT 5.0; U) X-Accept-Language: en MIME-Version: 1.0 Cc: Postgres SQL Mailing List Subject: Re: sequential joins References: Content-Type: text/plain; charset=koi8-r Content-Transfer-Encoding: 7bit X-Archive-Number: 200203/16 X-Sequence-Number: 6898 Ok, here it is: SELECT * FROM activity a LEFT OUTER JOIN (SELECT username AS artistname, objectid AS userid FROM user) u1 ON a.artist = u1.userid; After this join, I would have a view, that contains original activity table data, appended with column artistname, which would contain artist username, referenced from this activity record. If I join the result with (SELECT username AS programmername, objectid AS userid FROM user) u2 ON a.programmer = u2.userid I would have programmername column appended, containing programmers username, etc. I hope this clarifies the issue. thanks, Josh Berkus wrote: > Oleg, > > > 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? > > No, now I'm confused. Can you explain better what you are trying to > achieve as a result? > > -Josh > > ------------------------------------------------------------------------ > > ------------------------------------------------------------------------ > > ------------------------------------------------------------------------