Hello,
I have three simple tables

PRODUCT
id
name
manufacturer

FEATURE
id
name
sortorder

PRODUCT_FEATURE
id
product_id
feature_id
value

I am trying to build a search based on Product name and manufacturer and a feature value, where feature sql should be something like
((feature_id=1 and value in ("V1","v2") and (feature_id=2 and value in ("V1","v2"))

When a single feature select executed it returns results, when more than one feature selected it returns empty result set. I realized that feature_id cannot equal 1 and 2 at the same time, so my question to you SQL gurus how can I structure my sql statement to include all fields from product and product feature tables? Can I create a single view to incorporate all that data?

I am using Hibernate 3.1 rc3, with MySql, but that it is irrelevant, since my question is sql related and I don't know any other good forum to post it on
Thank you

If I understand you correctly, you want to have multiple feature results for a single product, each feature as its own column. Is that right?

If so, what you need to do is join against the tables more than once:



Does that give you what you need?

Thank you, however I have 24 feature search criteria, does that mean I have to join PRODUCT_FEATURE 24 times?? Is there a way to create a VIEW?

Second question, how can I build this query in Hibernate either using criteria or HQL query?

Thank you for all your help
Roman

If you want them as column data then yes, you would have to do all those joins.
Preferably, you could just rely on a regular row based join and your query is much simpler.



and you'll get one row for each product/feature pairing.

If you still insist on the column based result, the only help a view could give would be a view that links the PRODUCT_FEATURE.product_id to the FEATURE.name, simplifying your query a bit. So:



Then your original query gets simplified as such:



Frankly, I don't even know if this query might push the limit on how many tables you can join against. The fact that the view exists is just a convenience but at execution time will still be expanded out. You might be better off selecting the results of the view into a temp table first.


If I may ask, what is your motivation for doing this at all?

(btw, please rate my posting)

Thank you for your reply. I am dealing with advanced search dilema, where a user can search based on product feature values. For example, a user may choose to select that a certain product may contain feature 1, feature 2, and so on.. By contain, I mean values in ("Std","Opt"), just an example. So for every feature I must add a join, which I guess cannot be solved using cirteria model, or can it? What does criteria.createCriteria("someAlias") method do?

I can build out HQL for each feature id add a join, but that means I have to rewrite other product search criterias. The schema I provided is the simplified version, product table contains another 20 searchable fields and three more searchable join tables...
Thanks for your help