There is the following filter:

enter image description here

There is a table of products in the database, there is a table of product_countires (ratio M: M), which contains data on the producing country, because there may be a lot of them in a bouquet.

How to handle this filter? What query needs to be made in a DB? Those. In addition to price ( SELECT * FROM products WHERE price > :price_smallest AND price < price_biggest ), you need to somehow select by manufacturer, i.e. do I need to join the table? But how?

  • 3
    Well, actually the most common join. since you know such a word, you don’t know how to do it. for Google and this site will give a bunch of examples and all of them elementary according to this word - Mike

1 answer 1

LEFT JOIN easily copes with this, instead of id replacing with the desired identifier for comparison, you can also use the abbreviations AS

 SELECT * FROM products LEFT JOIN product_countires ON products.id = product_countires.id WHERE price.products > :price_smallest AND price.products < :price_biggest AND ... AND ...
  • And why LEFT, and not the usual INNER? if the condition on the country is set in the where part, then the effect of the LEFT will be gone. And if you specify ON, it will not work as a filter - Mike
  • Why in where there will be no effect? - Bert
  • I did not say that there would be no effect from WHERE. I said that there would be no effect from LEFT - to issue records from the first table, regardless of the presence of records in the second. If there is a condition (besides checking for is null) in the where part, then the left will be equivalent to inner, it will only confuse the optimizer and it may accept an incorrect execution plan. And I also said that if the condition on a country is placed on ON and left left, the condition will not work, because left will find all records regardless of their presence in the second table and regardless of any conditions on the second table in ON - Mike
  • Understood what's the matter, thank you, you need to work on it somehow, in fact the premise of building the query is correct, based on it you can already make a suitable one through INNER - Bert