There are tables:

groups (id, name) с данными: 1 Яблоки 2 Груши 3 Дыни products (id, name, group_id) с данными: 1 Семеринка 1 2 Мелба 1 3 Антоновка 1 products_copy (id, group_id, product_id) с данными 1 2 1 2 3 1 3 2 2 4 2 3

Those. Semerinka apples are also shown in groups of Pears and Melons, Melba and Antonovka in the group of Pears.

The question is how to make a sample of groups with the result 1,2,3. Those. find main group and duplicates from products_copy. You can also use php.

  • Did not quite understand what you want to receive as a result of the request ?? can somehow explain something else? or even better write what should be the result in a table? - Ale_x
  • One product can lie in different groups (products and products_copy), I need to find all these groups (in the example above, these are id groups 1,2,3). So clearer? - Sharpness
  • one
    And so no. - Denis Khvorostin
  • You need to select all the id groups for the product, which is in the products_copy table and products, right? - Sharpness
  • @ Sharp, and why even specify the group id in the products table? If I correctly understood how everything is arranged, you can get rid of it, and make the product_groups table (instead of products_copy), in which you enter the sign "main group". At the same time, a potential problem will be solved, when a single product may have several main groups. - user6550

2 answers 2

If you correctly understood the secret intent:

 select name, group_concat(cast(pc.group_id as char)) from products_copy pc join products p on pc.product_id=p.id group by name;
  • Yes, what you need! - Sharpness

As far as I understood the task, I decided so much:

 SELECT DISTINCT t1.id, t2.name, t1.name FROM `groups` as t1, `products` as t2, `products_copy` as t3 WHERE (t1.id = t2.group_id OR t1.id = t3.group_id) AND t2.id = t3.product_id

The idea is as follows: we join tables, products and products_copy from them we choose the id of the group, which is equal to products . group_id or products_copy . group_id

According to these tables, I got the result:

 id | name | name 1 | Семеринка | Яблоко 2 | Семеринка | Груша 3 | Семеринка | Дыня 1 | Мелба | Яблоко 2 | Мелба | Груша 1 | Антоновка | Яблоко 2 | Антоновка | Груша

To get the 1,2,3 required for you, you need to change the output from the select

  • Hmm, absolutely everything was chosen, how can we now determine which group belongs to which double? ZY You can use php to solve. - Sharpness
  • hmm, in fact, here are the tables:> FROM groups as t1, products as t2, products_copy as t3. From the necessary table simply you pull out the data, or you filter on it. - IVsevolod