pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Russell Shaw <rjshaw@iprimus.com.au>
Cc: Lista dyskusyjna pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Outer join
Date: Tue, 03 Feb 2004 17:44:13 +1100
Message-ID: <401F433D.2040801@iprimus.com.au> (raw)
In-Reply-To: <401F412B.5090802@klaster.net>
References: <401F3F7F.7020802@iprimus.com.au>
	<401F412B.5090802@klaster.net>

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)



view thread (7+ messages)

Message-ID: <401F433D.2040801@iprimus.com.au>
Permalink:  ../401F433D.2040801@iprimus.com.au/
Also on:    postgresql.org/message-id/401F433D.2040801@iprimus.com.au

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: rjshaw@iprimus.com.au
  Subject: Re: Outer join
  In-Reply-To: <401F433D.2040801@iprimus.com.au>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox