Re: combining tables

From: Cliff Nieuwenhuis <cliff(at)nieusite(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: combining tables
Date: 2008-10-27 01:44:37
Message-ID: 20081026204437.641792fa@killick
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

G. J. Walsh writes:

> > 1 of the 4 tables has the complete range of keys, in this case about
> > 1,000. The other 3 tables have data representing most but not all of
> > those keys, and in different sets. I want to end up with a new
> > 'combined' table which will allow me to immediately 'see' missing
> > data from the 3 smaller tables and take the necessary steps to
> > 'fill in the blanks'. I realize I would of course have to create
> > that table without constrictions other than the primary key.

and

> My <<< shaky >>> understanding from the manual is that I could use
> something like:
>
> SELECT * FROM tmain,tsub1,tsub2,tsub3 NATURAL INNER JOIN tcombo
>
> Am I on the right track?

I asked

> Do you mean you want to find keys that exist in the 'main' table but
> have no match in the other three tables?

.. and you said 'yes'. There is more to it on your side, but (to me)
those details are unique to what you are doing with your application.

For me (and I'm a novice as well and speak under correction here), I
will start will a query I can easily understand and then work towards
one that is more efficient. In your case I might try something like:

SELECT * FROM tmain WHERE tmain.primarykey NOT IN (
SELECT DISTINCT foreignkey FROM tsub1 );

..to get records that are in the 'main' table but have no related
information in tsub1. You would need to repeat this for the other
tables of course.

If you wanted a big table with all 'missing' fields, I would try an
OUTER JOIN instead of the NATURAL INNER JOIN, because you want the
records in the 'main' table to appear in the results even if there is
no match in one of the 'sub' tables.

Perhaps someone on this list has a better idea or understands your
situation better than I do.

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message G. J. Walsh 2008-10-27 17:00:31 Re: combining tables
Previous Message SC 2008-10-26 14:31:02 [ECPG] Characters set, w_char & co ...