There are three tables - Item , Detail and DetailsItem . Between themselves, they create a many-to-many connection. How can I implement Item Search on defined Detail .

For example, there is a hammer . It consists of several parts, such as: iron , wood , copper . I need to find this hammer in detail. For example, I drive in parameters such as wood and copper which will exactly find the desired element, but if I add an additional parameter such as silicone , then this element is discarded.

Also, according to these parameters, there may be other elements that contain iron, wood and copper, but if one of the parameters does not match, then the element should not be displayed in the final sample.

 SELECT item.idItem, detail.idDetail, detail.title FROM item JOIN details_item ON item.idItem = details_item.idItem JOIN detail ON detail.idDetail = details_item .idDetail WHERE detail.title in ("Железо","Дерево","Медь")

This query finds the elements in which there are these parts, but other elements are also displayed, which have only 1 of 3 materials matching.

It is also not known in advance how many parameters there will be for searching, since an array will be passed to in (...)

    1 answer 1

    I propose this solution:

     SELECT i.id, i.title FROM item i JOIN ( SELECT idItem, COUNT(idDetail) as qtyDetail FROM details_item di JOIN detail d ON di.idDetail = d.id WHERE title IN ('Медь', 'Олово') GROUP BY idItem ) q ON i.id=q.idItem AND q.qtyDetail=2

    In it the names of the parts are transferred separately ( 'Медь', 'Олово' ) and separately - their number ( 2 )

    Db-fiddle example

    It would be possible to calculate the number of transferred parts directly in the request, but in this case either would have to fence the transfer of names as a two-dimensional array (('Медь'), ('Олово')) , or count the number of found id in the table of details.

    The first option is bulky.

    The second option is bad because if we gave a non-existent part, it would simply be discarded, and the request would return data on the requested existing parts - it seems to me wrong.