Received: from mail1.waterford.org (mail1.waterford.org [205.124.117.6]) by postgresql.org (Postfix) with ESMTP id F3E6247593A for ; Fri, 1 Mar 2002 14:58:04 -0500 (EST) Received: from waterford.org ([205.124.138.93]) by mail1.waterford.org (Netscape Messaging Server 3.56) with ESMTP id 366 for ; Fri, 1 Mar 2002 12:46:02 -0700 Message-ID: <3C7FE2DF.2AF0A2BC@waterford.org> Date: Fri, 01 Mar 2002 13:21:51 -0700 From: Oleg Lebedev X-Mailer: Mozilla 4.72 [en] (Windows NT 5.0; U) X-Accept-Language: en MIME-Version: 1.0 To: Postgres SQL Mailing List Subject: sequential joins Content-Type: text/plain; charset=koi8-r Content-Transfer-Encoding: 7bit X-Archive-Number: 200203/12 X-Sequence-Number: 6894 You guys and gals were really helpful! I hope you can help me with this problem too. I have an Activity record, that has fields like artist, designer, programmer containing ids of users from User table, which are assigned to the current activity. What I need to do is create a view, that would contain all the Activity information, except artist, designer, and programmer should be filled out with corresponding usernames from the User table. Here is one way to do this: Schemas: Activity: name, artist, designer, programmer User: username, objectid SELECT name, artistname, designername, programmername FROM (SELECT * FROM activity a LEFT OUTER JOIN (SELECT username AS artistname, objectid AS userid FROM User) n1 ON a.artist = n1.userid LEFT OUTER JOIN (SELECT username AS designername, objectid AS userid FROM User) n2 ON a.designer = n2.userid LEFT OUTER JOIN (SELECT username AS programmername, objectid AS userid FROM User) n3 ON a.programmer = n3.userid) names; I wonder if there is a better way to do this. Maybe using CASE WHEN THEN ELSE END clause to avoid multiple scans? thanks, Oleg