CODEWITHSUNDEEP
mysql
Odin
Odin

Reputation: 157

Find the same string in mysql table between certain column values

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.

Upvotes: 1

Views: 85

Answers (2)

Sebastian Brosch
Sebastian Brosch

Reputation: 43604

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).

Upvotes: 2

Sandeep
Sandeep

Reputation: 119

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'

Upvotes: 2

Related Questions