By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,607 Members | 1,758 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,607 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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.