Re: SQL relating to foreign key table

John wrote:

> I need to write a small block of SQL code and was looking for some
> advice on the "correct" way to do this. Let's say I have three
> tables, one is a list of products, which is already populated with
> product info. The third table has a list of ways a product can be
> packaged. Of course all products are not packaged in all ways. The
> second table is where I will store the list of each product and have
> it is packaged. So I have a foreign relation from the product table
> to the way packaged table. And a foreign relation from the way
> packaged table to the list of packaging type. Ok, so I am importing a
> list of product codes and the way it's packaged, one line for each
> package method. I want to do an insert into the second table with the
> product number and the reference to the third table that matches the
> package method in each line. Can someone show me a sample of how to
> formulate a SQL code to say I have this Package method, what is the
> unique identifier in the third table that goes with this so I can put
> that value in the second table? Thanks.
>
> JR

You have missed an important part of what needs to be handled. What do you do if any of the following exist?

1. A product not in table 1
2. A packaging method not in table 3

Assuming this never happens all you need to do is insert. If not you need to write some

SELECT COUNT(*)
INTO i
FROM <table>

to determine whether something already exists and, if not, add it to table1 and/or table3.

Daniel Morgan Received on Sun Apr 06 2003 - 13:01:44 CDT