Find the same string in mysql table between certain column values

Question

I have a table in MySQL database let's call it products:

id_product | id_lang | name
-----------+---------+-----
       654 |       1 | abc
       654 |       2 | xyz
       654 |       3 | abc
       456 |       1 | qwe
       456 |       2 | rty
       456 |       3 | zxc
       123 |       1 | bnm
       123 |       2 | bnm
       123 |       3 | jkl
       789 |       1 | ert
       789 |       2 | tyu
       789 |       3 | ert
       333 |       1 | ddd
       333 |       2 | o
       333 |       3 | bbb
       222 |       1 | xxx
       222 |       2 | zzz
       222 |       3 | o

I would like to find all id_product which have the same name in id_lang 1 and 3. So result in this case should be id_product: 654 and 789. It would be great if in this query could find also all id_product where id_lang is 3 and name is "o". So result should be id_product: 654, 789 and 222. But if it is too complicated I can have two queries and merge two array's in PHP.

Sebastian Brosch · Accepted Answer · 2019-11-16 15:35:55Z

2

You can use the following solution using GROUP BY and HAVING. With UNION you can merge two querys to get one result:

SELECT id_product 
FROM products
WHERE id_lang IN (1, 3) 
GROUP BY id_product, name 
HAVING COUNT(name) > 1
UNION
SELECT id_product
FROM products
WHERE id_lang = 3 AND name = 'o'

demo on dbfiddle.uk

To get your first expected result you can use the following query:

-- result of this query: 654 and 789
SELECT id_product 
FROM table_name 
WHERE id_lang IN (1, 3) 
GROUP BY id_product, name 
HAVING COUNT(name) > 1

To get your second result you can use the following query:

-- result of this query: 222
SELECT id_product
FROM table_name
WHERE id_lang = 3 AND name = 'o'

To join these two results you can merge the querys by using UNION (see solution at the beginning).

edited Nov 16, 2019 at 15:35

answered Jun 8, 2017 at 21:31

Sebastian Brosch

43.8k15 gold badges78 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sandeep · Accepted Answer · 2017-06-08 21:37:30Z

2

Including the first part of the query from @Sebastian and adding the second piece with a Union

SELECT 
    id_product
FROM
    table_name
WHERE
    id_lang IN (1 , 3)
GROUP BY id_product , name
HAVING COUNT(name) > 1    
UNION
SELECT 
    id_product
FROM
    table_name
WHERE
    id_lang = 3 AND name = 'o'

answered Jun 8, 2017 at 21:37

Sandeep

1191 silver badge8 bronze badges

Collectives™ on Stack Overflow

Find the same string in mysql table between certain column values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related