X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 9E370D1D84E for ; Tue, 3 Feb 2004 06:44:37 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 70969-03 for ; Tue, 3 Feb 2004 02:44:31 -0400 (AST) Received: from smtp02.syd.iprimus.net.au (smtp02.syd.iprimus.net.au [210.50.76.52]) by svr1.postgresql.org (Postfix) with ESMTP id B2DC5D1D2E0 for ; Tue, 3 Feb 2004 02:44:31 -0400 (AST) Received: from iprimus.com.au (211.27.78.47) by smtp02.syd.iprimus.net.au (7.0.024) (authenticated as rjshaw) id 401146440034AA40 for pgsql-sql@postgresql.org; Tue, 3 Feb 2004 17:44:16 +1100 Message-ID: <401F433D.2040801@iprimus.com.au> Date: Tue, 03 Feb 2004 17:44:13 +1100 From: Russell Shaw User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.6) Gecko/20040122 Debian/1.6-1 X-Accept-Language: en MIME-Version: 1.0 Cc: Lista dyskusyjna pgsql-sql Subject: Re: Outer join References: <401F3F7F.7020802@iprimus.com.au> <401F412B.5090802@klaster.net> In-Reply-To: <401F412B.5090802@klaster.net> Content-Type: text/plain; charset=ISO-8859-2; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/28 X-Sequence-Number: 16719 Tomasz Myrta wrote: > Dnia 2004-02-03 07:28, Użytkownik Russell Shaw napisał: > >> Hi, >> I'm using postgresql 7.3.4 on debian. I get bad results >> from a two-table left outer join. >> >> First table: select * from descriptions; >> >> desc_id | description >> ---------+------------- >> 909097 | cap >> 107890 | resis >> 223940 | ic >> 447652 | electro >> (4 rows) >> >> >> Second table: select * from parts; >> >> part_id | desc_id | mounting | man_id >> ---------+---------+----------+-------- >> 2 | 107890 | SMD | 7 >> 1 | 909097 | LEADED | 1 >> 3 | 223940 | LEADED | 8 >> (3 rows) >> >> >> Join: >> SELECT parts.*, d.description, d.desc_id FROM parts p LEFT OUTER JOIN > > ^^^^^^^^^^^^^^^ > You can't access "parts" here - you used table alias, so the only way to > access it is using "p.*" > >> descriptions d ON p.desc_id=d.desc_id; >> >> NOTICE: Adding missing FROM-clause entry for table "parts" > > Rewrite your query and show your results. Thanks, it works now:) SELECT p.*, d.description, d.desc_id FROM parts p LEFT OUTER JOIN descriptions d ON p.desc_id=d.desc_id; part_id | desc_id | mounting | man_id | description | desc_id ---------+---------+----------+--------+-------------+--------- 2 | 107890 | SMD | 7 | resis | 107890 3 | 223940 | LEADED | 8 | ic | 223940 1 | 909097 | LEADED | 1 | cap | 909097 (3 rows)