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).
Auction
vin-char(8)
other-field(..)
other-field2(..)
AuctionId
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
fields together.
Hope this helps
-----Original Message-----
From: R.Stevens <raimee@ij.net>
To: Postgres-General <pgsql-general@postgreSQL.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
>problem:
>
>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,
>etc...
>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...
>
>BlackBook Auction
>------- -----
>vin -char(8) vin-char(17)
>year -char(1) AuctionId
>make
>model
>
>etc....
>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
>skipped.
>
>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??
>
>