This question is an exact duplicate:

Already the third hour I puzzle how to write the correct request, but nothing leaves.
The task is as follows:
There is a table of cars

auto (автомобили) id | name ------------- 1 | Volvo 2 | Audi 3 | Toyota

There is a table of spare parts

 spare (запчасти) id | name ------------- 1 | Колеса 2 | Двигатель 3 | Руль

There are many to many table for connecting parts and cars

 spare_to_auto auto_id | spare_id ------------------ 1 | 1 1 | 2 1 | 3 2 | 1 2 | 2

You need to get a list of cars whose parts list matches from two or more. And they need to be sorted in order of coincidence from large to small. Parts list is set by the user in the filter

That's what I got in the end

 SELECT * FROM auto as a INNER JOIN spare_to_auto AS s_to_a ON a.id = s_to_a.auto_id INNER JOIN spare AS s ON s_to_a.spare_id = s.id AND s.id IN ($список_запчастей) GROUP BY a.id

But unfortunately this request does not work correctly.

Reported as a duplicate by Akina , rjhdby , Denis Bubnov , Kromster , Sasha Omelchenko on Mar 6 '17 at 12:17 .

This question has been marked as a duplicate of an existing one.

  • How is this question different from this ? - andreymal
  • There you need to get a list of cars in which the common parts completely match. And here is partly - XYZ
  • And in that question you accepted not the best answer, it is certainly a worker, but the one that I indicated there as a double is better because allows not to glue the table many times and most importantly, if you remove the condition having and add order by count(1) desc just gives sorting by the number of matches. so besides, what you wrote here is just such an order by add and everything - Mike

2 answers 2

 SELECT * FROM auto as a INNER JOIN spare_to_auto AS s_to_a ON a.id = s_to_a.auto_id WHERE s_to_a.spare_id IN ($список_запчастей) GROUP BY a.id HAVING count(1)>1 ORDER BY count(1) desc

I have removed the spare table from the query, since the sample is based on the spare parts ID, which is already in spare_to_auto . By the way, the search for all spare parts differs only by changing the condition in having an equality to the number of those sought.

     select name from auto as a inner join ( select i1.auto_id,count(1) as count from spare_to_auto as i1 inner join spare_to_auto as i2 on i1.spare_id=i2.spare_id and i1.auto_id<>i2.auto_id group by i1.auto_id) as b on a.id=b.auto_id and b.count>1
    • one
      Complete your answer with a detailed description of the solution to the problem or a code to make it easier for users to understand your answer. - Yuri