Developer forum

Product and Groups are a many-to-many relation and there is a table between the to entities called EcomGroupProductRelation that you have to look in to find its group association.

So you need EcomGroups joined on EcomGroupProductRelation joined on EcomProducts.

Like this:
SELECT EcomGroups.GroupID, EcomGroups.GroupNumber, EcomGroups.GroupName, EcomProducts.ProductID, EcomProducts.ProductNumber, EcomProducts.ProductName
FROM (EcomGroupProductRelation INNER JOIN EcomGroups ON EcomGroupProductRelation.GroupProductRelationGroupID = EcomGroups.GroupID) INNER JOIN EcomProducts ON EcomGroupProductRelation.GroupProductRelationProductID = EcomProducts.ProductID
WHERE EcomGroups.GroupNumber="MyGroupNumber"

And you can use datalists with a custom view to specify the SQL.

hi Nicolai,

thanks for the answer.

but when I use the SQL statement, the list says "no data". is there some setup or preparations I have to make?

thanks
//Peter Bille

You have to change the "MyGroupNumber" in the where clause with a groupnumber you have - or remove the where clause to get all the products...

awesome, awesome.. I got it.

now its easy to make a custom datalist of all the tables in the DB i guess? .if they can be joined.

thanks! :-)