Many to Many relationship in Dataset

Hello

I've got a situation where I have one product could be in many categories. To do this, I have a middle table which basically contains categoryid and productid. What I want to be able to do is say Get all Products given this category Id.

I've loaded up all the data into the dataset - and then I'm on the middle table - but the only way I can see to do it is to loop through each matching row in the middle table, find the product Id's, get the associated row, add it to something like an array or a list, and then bind that to the gridview.

This seems eternally messy and I wondered if there was a better way of doing this?

Many thanks, Paul

I'd simply embed a Repeater within a TemplateField of your GridView. Then programatically set the DataSource for your embedded Repeater. Here's a great article on nested data web controls. Pay particular attention to the section entitled 'Accessing the Products by Category Data Programmatically'.

http://www.asp.net/learn/data-access/tutorial-32-cs.aspx

Thank you...yeah, that looks like a good article. but it only really handles situations when one product is in one category, it doesn't handle multiple categories per product (or anything else - it doesn't have to be products)

Paul