My advice is to make the schema a little simpler.
Break out the Auction's vin-char(17) into different fields, with one field
being related to the BlackBook's vin-char(8).
Then you can do an outer join between the two tables by using the following:
... where blackbook.vin-char = auction.vin-char ...
If you want to get the Auction's different fields together, just append the
Hope this helps
From: R.Stevens <raimee(at)ij(dot)net>
To: Postgres-General <pgsql-general(at)postgreSQL(dot)org>
Date: Thursday, August 06, 1998 9:15 PM
Subject: [GENERAL] Design
>I really only need to start with a good plan. I have the following
>I get data from two text files. The first file contains BlckBook data
>on all vehicles. It contains fields such as vin, make, model, year,
>The second file contains vehicle data specific to actual vehicles sold
>on an auction. Some fields in this file are vin, amount the vehicle
>auctioned for, Auctioners ID, etc...
>vin -char(8) vin-char(17)
>year -char(1) AuctionId
>The files are related by the following:
>BlackBook.vin + BlackBook.year == Auction.vin(1-8) + Auction.vin(10)
>That is, the BlackBook vin field and year field match the Auction vin
>field characters 1 through eight plus character ten. Character nine is
>I need to access the Black Book Data (fields including make, blackbook
>value, etc..) for a given vehicle in the Auction File. I am not sure
>of the best way to accomplish this.
>Eventually I will have approx. 200K records in the Auction file and
>about 82K records in the BlackBook file.
>I see only one option:
>1) Build a third table (or view, Although multiple views will be needed
>later to establish levels of authorization).
>This third table can be construced by reading records from the Auction
>File (table), matching a valid vin number and then selecting required
>fields from both
>tables and copying them to the New table. My problem here is with the
>implementation. Do I use a subquery? Or a function? Not sure how to
>build the function !
>I need some help here. Any suggestions??
pgsql-general by date
|Next:||From: Chris Hardie||Date: 1998-08-08 00:49:58|
|Subject: Postgres CGI Security Problem|
|Previous:||From: Tom Good||Date: 1998-08-07 12:42:05|
|Subject: Re: [GENERAL] Switch database inside psql?|