That returns products with any of the given features. The OP wanted products with ALL the given features.
/G 2011/6/11 Maximilian Raditya <[email protected]> > 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. > -- 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.
