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
ProductCategori es
------------------
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
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
ProductCategori es
------------------
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
Comment