Skip site navigation (1) Skip section navigation (2)

removing duplicate entries in a <JOIN> statement

From: kvnsmnsn(at)cs(dot)byu(dot)edu
To: pgsql-novice(at)postgresql(dot)org
Subject: removing duplicate entries in a <JOIN> statement
Date: 2007-04-27 15:18:59
Message-ID: 35544.67.137.192.66.1177687139.squirrel@mail.cs.byu.edu (view raw or flat)
Thread:
Lists: pgsql-novice
G'morning all,

     My task is to take table <abc> and join it with <def> with
<WHERE> clause <WHERE abc.ghi = def.ghi>.  My immediate reaction is to
write SQL statement:

          SELECT
            a.*, d.p
          INTO
            output_table
          FROM
            abc a LEFT OUTER JOIN def d
          ON
            a.ghi = d.ghi;

The problem with this is that <def> has some duplicate entries, seve-
ral occurrences of a row that has precisely the same column values as
some other row in <def>, so I'm getting duplicate rows in table
<output_table>.

     I've found one way to get around that; I wrote SQL statement:

          SELECT
            a.*, d.p
          INTO
            output_table
          FROM
              abc a
            LEFT OUTER JOIN
              (SELECT * FROM def UNION SELECT * FROM def) d
          ON
            a.ghi = d.ghi;

Since I'm doing a <UNION>, that gets rid of all the duplicate rows.
This solves my problem, but it seems to take an enormous amount of
time to execute.  Is this the best way to remove duplicate entries, or
is there some other way?

                                ---Kevin Simonson

"You'll never get to heaven, or even to LA,
if you don't believe there's a way."
from _Why Not_


Responses

pgsql-novice by date

Next:From: kvnsmnsnDate: 2007-04-27 16:10:38
Subject: including limited lines depending on input parameter
Previous:From: stephenDate: 2007-04-27 00:26:14
Subject: Re: cannot insert or update

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group