469,275 Members | 1,781 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,275 developers. It's quick & easy.

Using a "match" table to store multiple columns for parent data

Sorry for the confusing subject. Here's what im doing:

I have a table of products. Products have N categories and
subcategories. Right now its 4. But there could be more down the
line so it needs to be extensible.

So ive created a product table. Then a category table that has many
categories of products, of which a product can belong to N number of
these categories. Finally a ProductCategory "match" table.

This is pretty straigth forward. But im getting confused as to how to
write views/sprocs to pull out rows of products that list all the
products categories as columns in a single query view.

For example:

lets say productId 1 is Cap'n Crunch cereal. It is in 3 categories:
Cereal, Food for Kids, Crunchy food, and Boxed.

So we have:

Product
----------------
1 Capn Crunch

Categories
-----------------
1 Cereal
2 Food for Kids
3 Crunchy food
4 Boxed

ProductCategories
------------------
1 1
1 2
1 3
1 4

How do I go about writing a query that returns a single result set for
a view or data set (for use in a GridView control) where I would have
the following result:

Product results
---------------------------------
ProductId ProductName Category 1 Category 2
Category 3 Category N ...
------------------------------------------------------------------------
1 Capn Crunch Cereal Food for Kids Crunchy food
Boxed
Am I just thinking about this all wrong? Sure seems like it.

Cheers,

Will
Feb 27 '08 #1
1 1466
On Feb 27, 2:02 pm, wfsm...@gmail.com wrote:
Sorry for the confusing subject. Here's what im doing:
If by chance you're still doing what you were doing (going in
circles?:) you can straighten out your trajectory with some help from
the Rac utility. Rac will easily produce any kind of dynamic crosstab
with no sql coding. We only require you to figure out what result you
want not how to do it :)
Visit Rac @
www.rac4sql.net

www.beyondsql.blogspot.com
Mar 1 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by kmunderwood | last post: by
1 post views Thread by DKode | last post: by
1 post views Thread by Pythor | last post: by
3 posts views Thread by Good Man | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.