Postgres Pro
Downloads
Home   >   mailing lists

Thread: Outer join

Outer join

From
Russell Shaw
Date:
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
(4rows)
 


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 
descriptions d ON p.desc_id=d.desc_id;
  NOTICE:  Adding missing FROM-clause entry for table "parts"
   part_id | desc_id | mounting | man_id | description | desc_id
---------+---------+----------+--------+-------------+---------        2 |  107890 | SMD      |      7 | resis       |
107890        1 |  909097 | LEADED   |      1 | resis       |  107890         3 |  223940 | LEADED   |      8 | resis
   |  107890         2 |  107890 | SMD      |      7 | ic          |  223940         1 |  909097 | LEADED   |      1 |
ic         |  223940         3 |  223940 | LEADED   |      8 | ic          |  223940         2 |  107890 | SMD      |
  7 | cap         |  909097         1 |  909097 | LEADED   |      1 | cap         |  909097         3 |  223940 |
LEADED  |      8 | cap         |  909097  (9 rows)                /\                                          /\
       ||                                          ||              p.desc_id
d.desc_id

I don't see why there are rows with p.desc_id and d.desc_id different.
(I learnt sql last week)

Re: Outer join

From
Tomasz Myrta
Date:
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.

Regards,
Tomasz Myrta

Re: Outer join

From
Russell Shaw
Date:
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)