How to create a SQL query to select the values ​​of the field "Name 1", in which all pairs of "Parameter" + "Value" are equal to the pairs "Parameter" + "Value" from the second table.

For example, the first table:

+--------------------------------------+ | ID Имя 1 Параметр Значение | | 1 Товар 1 Длина 50 | | 2 Товар 1 Ширина 100 | | 3 Товар 2 Длина 80 | | 4 Товар 2 Ширина 100 | +--------------------------------------+

Second table

 +----------------------------------------+ | ID Имя 2 Параметр Значение | | 1 Продукт 1 Длина 50 | | 2 Продукт 1 Ширина 100 | | 3 Продукт 2 Длина 25 | | 4 Продукт 2 Ширина 100 | +----------------------------------------+

Thus, product 1 corresponds to product 1. And the search query would display “Product 1”

  • one
    ru.stackoverflow.com/questions/568826/… - Mike
  • I do not really understand how this can be applied here. Could you explain the logic of applying the example to this task? - Ivan Sokolov
  • And to start, tell me how to connect these two tables. I see in them identical only parameter value. How to understand which rows of the second table should be considered for item 1, which for item 2. What if there is another product 3 in the second table and the same with a length of 50 and a width of 100. You’ll get two products that match the parameters with item 1 - Mike
  • Link them only by the coincidence of all the "parameters". That is, each "product" can have several "parameters" and you need to display all the "products" that match all the "parameters". That is, if there is a “product 3” with a length of “50” and a width of “100”, it must also be withdrawn - Ivan Sokolov

3 answers 3

We imply that the names of the parameters are not repeated within a single product or product!

Option 1. If the number of possible product parameters is known in advance (there are two in your example), then you can do this:

 SELECT t1.name1, t2.name2, COUNT(*) AS cnt FROM table1 AS t1 JOIN table2 AS t2 ON (t1.attr, t1.val) = (t2.attr, t2.val) GROUP BY name1, name2 HAVING cnt = 2

See http://sqlfiddle.com/#!9/a4136/6
Here we find parameter-value matches between products and products. Group in order to count the number of matches and filter by this number.

Option 2. The number of parameters is arbitrary and it is necessary to find such goods for which all the parameters described for the goods, no matter how many, are in some product.

 SELECT g1.* FROM ( SELECT t1.name1, t2.name2, COUNT(*) AS cnt FROM table1 AS t1 JOIN table2 AS t2 ON (t1.attr, t1.val) = (t2.attr, t2.val) GROUP BY name1, name2 ) AS g1 JOIN ( SELECT name1, COUNT(*) AS cnt FROM table1 GROUP BY name1 ) AS g2 ON (g1.name1, g1.CNT) = (g2.name1, g2.CNT)

See http://sqlfiddle.com/#!9/a4136/7
It took the second grouping query to calculate the required number of parameters.

     SELECT t1.id, t2.id FROM ( SELECT table1.id, GROUP_CONCAT(DISTINCT CONCAT(table1.param, table1.value) ORDER BY table1.param ASC) params FROM table1 GROUP BY table1.id ) t1, ( SELECT table2.id, GROUP_CONCAT(DISTINCT CONCAT(table2.param, table2.value) ORDER BY table2.param ASC) params FROM table2 GROUP BY table2.id ) t2 WHERE t1.params = t2.params;
    • Ps. There is insurance from duplicate records, but not from multi-values. If they are found, then ORDER BY also needs to put concatenation instead of one field. - Akina
      select A.a_name, A.b_name from ( select A.name as a_name, B.name as b_name, count(1) as cnt from Tab1 A, Tab2 B where B.parm=A.parm and B.val=A.val group by A.name, B.name ) A join (select name, count(1) as cnt from Tab1 group by name) AC on AC.name=A.a_name join (select name, count(1) as cnt from Tab2 group by name) BC on BC.name=A.b_name where A.cnt=AC.cnt and AC.cnt=BC.cnt
    • If I understand correctly, the request requires that the number of parameters for the product and the product be the same. But most likely, AC.cnt <= BC.cnt is enough. I put it on my column names: sqlfiddle.com/#!9/a4136/21 - artoodetoo
    • @artoodetoo So the vehicle did not correctly describe the task. According to his statement, I was counting on the search for a complete match, regardless of the number of parameters. - Mike
    • I didn’t clarify the task so much, according to the condition both query are true. Thank! - Ivan Sokolov