We have two tables PRODUCTS and CATEGORIES, both of them have a many-to-many relationship through a third table --> CATE_PRODUCTS

Name:  tables.jpg
Views: 772
Size:  36.2 KB

My question: given 1 or more ID_PRODUCT I need to retrieve all the COMMON Categories that belongs to those Products.

Example: Given 3 Products IDs:

PRODUCTS ---------- CATEGORIES of each one
--------------------------------------------------------------
COKE ------------> SelfService - Sale - **Beverage**
ORANGE JUICE ----> **Beverage**
VINE -------------> Licour - **Beverage**

In this case 'Beverage' is the only one common category value for those 3 given Products

I'm trying to do it through Linq To SQL in VB.NET, but not successful

With the code below I retrieve all Categories from all given Products:

Code:
Dim Query = (From Cate In db.CATEGORIAS Join CatePro In db.CATE_PRODUCTOS
                     On Cate.ID_CATEGORIA Equals CatePro.ID_CATEGORIA
                    Where MiList.Contains(CatePro.ID_PRODUCTO) Select Cate.CAT_NOMBRE)
But I need to retrieve ONLY the common Categories, not all Categories.

¿Any ideas?

Thanks a lot !!!