> SELECT * FROM Product JOIN ProductCategoryFeatureValue AS feature1 ON Product.id = feature1.ProductID AND feature1.categoryfeatureid = 643229 JOIN productcategoryfeaturevalue AS feature2 ON Product.id = feature2.ProductID AND feature2.categoryfeatureid = 667811
I wonder why you don't use IN over JOIN in there so it could be like this: SELECT * FROM Product (INNER) JOIN ProductCategoryFeatureValue AS feature ON Product.id = feature.ProductID WHERE feature.categoryfeatureid IN (643229, 667811) Could it be an issue? If NH Linq can't be used, perhaps you can use Criteria to express it. -- Regards, Maximilian Haru Raditya On Fri, Jun 10, 2011 at 8:41 PM, Karl Cassar <[email protected]> wrote: > I have a database schema, where I have a `Product`, `Category`, > `CategoryFeature`, and an `ProductCategoryFeatureValue`. > > Basically, the features available to a product are listed in the > `ProductCategoryFeatureValue` table, which is the 'middle-link' for > the many-to-many collection. > > I need to create a query, where i can find all products, which have > ALL the features selected by the user. > > Example, doing a search for two features with ids 643229 & 667811 in > SQL terms, I would do something like this: > > SELECT * FROM Product > JOIN ProductCategoryFeatureValue AS feature1 ON Product.id = > feature1.ProductID AND feature1.categoryfeatureid = 643229 > JOIN productcategoryfeaturevalue AS feature2 ON Product.id = > feature2.ProductID AND feature2.categoryfeatureid = 667811 > > Another query which I could do is this: > > SELECT * FROM product WHERE > ((SELECT id FROM productcategoryfeaturevalue AS feature1 WHERE > feature1.ItemGroupID = product.id AND feature1.categoryFeatureID = > 643229 LIMIT 1) IS NOT NULL) > AND > ((SELECT id FROM productcategoryfeaturevalue AS feature2 WHERE > feature2.ItemGroupID = product.id AND feature2.categoryFeatureID = > 667811 LIMIT 1) IS NOT NULL) > > Both have been tested and work well. However, I cannot seem to > reproduce them using NHibernate. Any ideas? > > Thanks a lot in advance! > > -- > You received this message because you are subscribed to the Google Groups > "nhusers" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/nhusers?hl=en. > > -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
