Re: Outer join

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)