CODEWITHSUNDEEP
mysqlcountgroup-byduplicateshaving
Dan
Dan

Reputation: 87

Finding duplicates from two columns, but show all rows MySQL

I have a table like this

| user_id | company_id | employee_id |
|---------|------------|-------------|
|    1    |      2     |     123     |
|    2    |      2     |     123     |
|    3    |      5     |     432     |
|    4    |      5     |     432     |
|    5    |      7     |     432     |

I have a query that looks like this

SELECT COUNT(*) AS Repeated, employee_id, GROUP_CONCAT(user_id) as user_ids, GROUP_CONCAT(username)
    FROM user_company
    INNER JOIN user ON user.id = user_company.user_id
        WHERE employee_id IS NOT NULL
        AND user_company.deleted_at IS NULL
        GROUP BY employee_id, company_id
        HAVING Repeated >1;

The results I am getting look like this

| Repeated | employee_id | user_ids   |
|---------|--------------|------------|
|    2    |      123     |   2,3      |
|    2    |      432     |   7,8      |

I need results that look like this

| user_id |
|---------|
|    2    |
|    3    |
|    7    |
|    8    |

I realize my query is getting more, but that's just to make sure I'm getting the correct data. Now I need to get a single column result with each user_id in a new row for updating based on user_id in another query. I've tried this by only selecting the user_id but I only get two rows, I need all four rows of duplicates.

Any ideas on how to modify my query?

Upvotes: 1

Views: 50

Answers (2)

Asheliahut
Asheliahut

Reputation: 911

This query below will generate the query you are looking for.

    SELECT CONCAT('UPDATE user_company SET employee_id = null WHERE user_id IN (', GROUP_CONCAT(user_id SEPARATOR ', '),')') AS user_sql
    FROM    user_company uc
    INNER JOIN 
    (SELECT employee_id, company_id
       FROM user_company 
       WHERE employee_id IS NOT NULL
       AND deleted_at IS NULL
       GROUP BY employee_id, company_id
       HAVING COUNT(employee_id) >1) AS `emps`
       ON emps.employee_id = uc.`employee_id`
       AND emps.company_id = uc.`company_id`;

Upvotes: 1

Jessica
Jessica

Reputation: 7005

Here is the query to get all of your user_ids:

SELECT user_id 
FROM user_company uc 
INNER JOIN 
( 
   SELECT employee_id, company_id
   FROM user_company 
   WHERE employee_id IS NOT NULL
   AND deleted_at IS NULL
   GROUP BY employee_id, company_id
   HAVING COUNT(employee_id) >1
) AS `emps`
ON emps.employee_id = uc.`employee_id`
AND emps.company_id = uc.`company_id`;

Upvotes: 2

Related Questions