pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Is it possible to use a field from another table as part of a query? 3+ messages / 2 participants [nested] [flat]
* Is it possible to use a field from another table as part of a query? @ 2002-07-19 18:37 Patrick Hatcher <PHatcher@macys.com> 0 siblings, 1 reply; 3+ messages in thread From: Patrick Hatcher @ 2002-07-19 18:37 UTC (permalink / raw) To: pgsql-sql I originally sent this question to the Novice group, but realized I should have asked it here: 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: 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? TIA Patrick Hatcher ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Is it possible to use a field from another table as part of a query? @ 2002-07-19 18:54 Josh Berkus <josh@agliodbs.com> parent: Patrick Hatcher <PHatcher@macys.com> 0 siblings, 0 replies; 3+ messages in thread From: Josh Berkus @ 2002-07-19 18:54 UTC (permalink / raw) To: Patrick Hatcher <PHatcher@macys.com>; pgsql-sql 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Is it possible to use a field from another table as part of a @ 2002-07-19 19:26 Patrick Hatcher <PHatcher@macys.com> 0 siblings, 0 replies; 3+ messages in thread From: Patrick Hatcher @ 2002-07-19 19:26 UTC (permalink / raw) To: josh@agliodbs.com; +Cc: pgsql-sql 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@agliodbs To: "Patrick Hatcher" <PHatcher@macys.com>, pgsql-sql@postgresql.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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-07-19 19:26 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2002-07-19 18:37 Is it possible to use a field from another table as part of a query? Patrick Hatcher <PHatcher@macys.com> 2002-07-19 18:54 ` Josh Berkus <josh@agliodbs.com> 2002-07-19 19:26 Re: Is it possible to use a field from another table as part of a Patrick Hatcher <PHatcher@macys.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox