0

I have 3 tables in a database. Product with product_id, Functionality with functionality_id and product_funcionality (product_funcionality_id, product_id, functionality_id)

I need a query to find products where you can select all products that contain the 'minimum' such functionality.

If the Product P1 has the F1, F5, F10 features

The query should find products that contain exactly the same features, F1, F5, F10, but it should show the products that have some added functionality.

You can display products with:

P1 -> F1, F2, F3, F5, F10,
P2 -> F1, F5, F6, F10,
P3 -> F1, F3, F5, F10, F11,

Can not display:

P4 -> F1, F2, F10,
P5 -> F1, F5, F11

I'm struggling to write a query to ensure that business rule, can not write 'find the products with at least such features'.

The IN clause does not address directly.

I do like to write SQL to get the products with at least such features? appreciate

EDIT:

I had to add the functionality_type table, and the queries are made ​​from the type of functionality, only you must follow the same structure described above, the table functionality_type that will be used to refer to all products that have a certain type of functionality. Changing the Having Clause to grab at least all products containing features with type_funtionality_id?

Each type of functionality is just a key with the name. Also funcionality is related with brands.

Mauricio Piber Fão
  • 810
  • 5
  • 21
  • what does the product_functionality_id column do? (this is a trick question) – Strawberry Feb 07 '14 at 12:10
  • @MichalBrašna - why not abandon SQL altogether? – Strawberry Feb 07 '14 at 12:11
  • Also product 1 appears to have additional features (F2 and F3), so do you want products with ALL the features of product 1, or just with the 3 specified features? – Strawberry Feb 07 '14 at 12:20
  • 1
    **[How to filter SQL results in a has-many-through relation](http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation)** – ypercubeᵀᴹ Feb 07 '14 at 12:50

2 Answers2

1

If you want to search based on list of features as minimal functionality, you can do:

    SELECT product_id,
           COUNT(*) AS features
      FROM product_funcionality pf
INNER JOIN funcionality f ON f.functionality_id = pf.functionality_id
     WHERE functionality_type_id = 1
  GROUP BY product_id
    HAVING features = 
   (SELECT COUNT(*)
      FROM funcionality
     WHERE functionality_type_id = 1)

Demo at http://sqlfiddle.com/#!2/e9d20/5

Michal Brašna
  • 2,293
  • 13
  • 17
  • @MauricioPiberFão Feel free to vote up or accept the answer. – Michal Brašna Feb 07 '14 at 21:27
  • I'll edit the issue, please take a look, the client asked to change the rule for type_functionality table, I'll select the types of features and all products have features of that type should appear. Read edited question please. – Mauricio Piber Fão Feb 10 '14 at 13:14
  • @MauricioPiberFão Updated my answer and link to demo. Also don't edit answered questions, noone is reading the old ones and accept correct answer. Thank you – Michal Brašna Feb 12 '14 at 10:24
  • Sorry by, iam newest here, i cant vote you up by my lower reputation, thanks for your help instead. – Mauricio Piber Fão Feb 12 '14 at 14:31
0

I think this should work for you:

select product_id, group_concat(functionality_id) from product_funcionality where functionality_id IN (:yourFuntionality) group by product_id

reply me if have any problem

murtaza.webdev
  • 3,523
  • 4
  • 22
  • 32