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

help / color / mirror / Atom feed
From: Russell Shaw <rjshaw@iprimus.com.au>
To: pgsql-sql@postgresql.org
Subject: Outer join
Date: Tue, 03 Feb 2004 17:28:15 +1100
Message-ID: <401F3F7F.7020802@iprimus.com.au> (raw)

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 
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)



view thread (7+ messages)  latest in thread

Message-ID: <401F3F7F.7020802@iprimus.com.au>
Permalink:  ../401F3F7F.7020802@iprimus.com.au/
Also on:    postgresql.org/message-id/401F3F7F.7020802@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: <401F3F7F.7020802@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