Re: Is it possible to use a field from another table as part of a

Thanks Josh! That worked.

~~~~~~~~~~
> I have a table that contains a VARCHAR field with data formatted as such:
> 12,44,13,225
> what I would like to do is use this field in a query to another table
such
> as:

1. Since you are a smart guy, I'm assuming that this "delimited" VARCHAR
data
is legacy data that you don't have a choice about re-structuring. Because,

of course, the normalized way to store the data would be in a subtable, not
a
VARCHAR field.
~~~~~
Actually, I had used this function
(http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=15)

because I needed to find and category and all its relative categories.
Then the boss decided he wanted to see all the products attached to any of
the categories.
DUH, now that you mentioned it, I could just dump the individual results to
a table instead of concat'ing them.
Thanks again!!

Patrick Hatcher

Josh Berkus
<josh(at)agliodbs To: "Patrick Hatcher" <PHatcher(at)macys(dot)com>, pgsql-sql(at)postgresql(dot)org
.com> cc:
Subject: Re: [SQL] Is it possible to use a field from another table as part of a query?
07/19/2002
11:54 AM
Please respond
to josh

Partick,

> I have a table that contains a VARCHAR field with data formatted as such:
> 12,44,13,225
> what I would like to do is use this field in a query to another table
such
> as:

1. Since you are a smart guy, I'm assuming that this "delimited" VARCHAR
data
is legacy data that you don't have a choice about re-structuring. Because,

of course, the normalized way to store the data would be in a subtable, not
a
VARCHAR field.

>
> CREATE TABLE category_tree (
> tree varchar(200)
> ) WITH OIDS;
>
> Select * from mdc_products
> where keyf_category_home IN (select tree from category_tree)
>
> However, my keyf_category_home field is an INT4. Is there a way to parse
> out the tree field so that I can define it as INT4?

Well, this is easiest thing to do:

Select * from mdc_products
where EXISTS
(select tree from category_tree
WHERE tree ~ ('(^|,)' || keyf_category_home || '(,
|$)'));

(somebody please correct my regexp if I've made an error)

... but that's impossible to index. If the table category_tree doesn't
change often, I'd write a program to parse the data and build a normalized
subtable containg a vertical colum of tree values.

--
-Josh Berkus
Aglio Database Solutions
San Francisco