Re: How can I do this?

On Wed, 8 Jan 2003 18:37:13 -0600 , "Boget, Chris" <chris(at)wild(dot)net>
wrote:
>I'm still in the process of converting my MySQL DB
>to PG. I've redone the table schemas so that they
>are normal form.
>
>SELECT ( SELECT cards_type.record_num FROM cards_type WHERE
>cards_type.card_name LIKE ( substr( trader_haves_old.card_name, 1, 9 ) ||
>'%' )), trader_haves_old.total_have, ( SELECT logins.record_num FROM logins
>WHERE logins.name = trader_haves_old.trader ), trader_haves_old.available
>from trader_haves_old;

Chris, why do you want to match on only the first nine characters of
card_name? A typical normalization job looks like:

-- record_num is supplied by DEFAULT clause
INSERT INTO cards_type(card_name)
SELECT DISTINCT card_name
FROM trader_haves_old;

-- record_num is supplied by DEFAULT clause
INSERT INTO logins(name)
SELECT DISTINCT trader
FROM trader_haves_old;

INSERT INTO trader_haves(card_id, total_have, trader_id, available)
SELECT c.record_num, o.total_have, l.record_num, o.available
FROM trader_haves_old o
INNER JOIN cards_type c ON c.card_name = o.card_name
INNER JOIN logins l ON l.name = o.trader;

HTH.
Servus
Manfred