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)