There are 5 tables (so split because of the size. Tables have units ~ 15k, params ~ 12k, units_params ~ 60k records):

units (id,name) colors (id,name) units_colors (unit_id,color_id) params (id,name) units_params (unit_id,param_id)

How best to search:

 в названии юнита есть фраза: 'car' есть цвет: 'black' и 'green' нет цвета: 'red' eсть параметр: 'wood'

At the moment, the search is performed first by name. A list of matching id is thrown into the array. After the second search is performed by color in the range of id from the array. etc. Is it possible to perform this operation with one MySQL query?

  • With one query, you can do anything. And yes, this should be done with a single request and nothing else. For the word join, see any questions for answers or google , for example, ru.stackoverflow.com/questions/618237 - Mike
  • Well, "no color" I think the easiest thing to do would be using " NOT EXISTS " - Mike
  • Here the connection is more complicated ... Units <-> Units_colors <-> Colors, and not just Units <-> Colors ... - Dm1tro
  • Who cares ? in the merge conditions, just specify the necessary columns from the necessary tables - Mike
  • Ok ... with a bunch of Units <-> Units_colors <-> Colors is clear .... but how to combine the Units_colors and Units_params tables with a join? Before sending to google ... read carefully the question. - Dm1tro

3 answers 3

 select * from colors where id in ( select units_colors.colors_id from units_colors, units where units_colors.unit_id= units.id and units.name like 'ваше значение' );

etc.

  • Yeah ... and syntax error. - Akina
  • Thank you ... Based it on ...) - Dm1tro

Exactly according to your conditions, I would write something like this:

 select * from units u where u.name like '%car%' and (2, 0)=(select sum(c.name in('black','green')), sum(c.name in('red')) from units_colors uc, colors c where c.id=uc.color_id and uc.unit_id=u.id) and exists(select 1 from units_params up, params p where up.param_id=p.id and p.name='wood' and up.unit_id=u.id)

Theoretically, if you need some kind of universal solution, you could stop at checking all the conditions in the same way that colors are checked in this query and only substitute the parameters into the sums "should be" and "should not be" and changing the expected number of matched parameters in the left part of the equality .

  • Thanks for the answer. Difficult for me, but I will try to make out what is what. - Dm1tro

Thank you all for the answers. I came to such a chain thanks to Xenia. Before that, what I was trying to do was having problems with the logic of the request. I haven’t disassembled Mike’s variant yet, it’s a bit complicated for me

 SELECT * FROM units WHERE name LIKE '%car%' AND id IN ( SELECT unit_id FROM units_params, params WHERE params.id=param_id AND params.name LIKE '%wood%' ) AND id IN ( SELECT unit_id FROM units_colors, colors WHERE colors.id=color_id AND colors.name = 'black' ) AND id IN ( SELECT unit_id FROM units_colors, colors WHERE colors.id=color_id AND colors.name = 'green' ) AND id NOT IN ( SELECT unit_id FROM units_colors, colors WHERE colors.id=color_id AND colors.name != 'red' )