Mysql – Detect with Left Join duplicates entries and return the first value, also return sum of duplicates entries

group byjoin;MySQLmysql-workbench

¡Hi!, i have a case with two tables than can have a lot of matches entries and the time of fetching increases a lot.

This tables can be

Table A: Employees

-------------------------
| Name   | ID | Account |
-------------------------
| Nicole | 01 | 12345   |
| Alexis | 02 | 67890   |
-------------------------

And Table B: BankAccounts

--------------------------
| Name   | ID  | Account |
--------------------------
| Nicole | 01  | 12345   |
| Nicole | 01  | 67890   | //duplicates Accounts
| Alexis | 02  | 67890   | //duplicates Accounts
--------------------------

And i want to do this with a Left Join in a Table that can have more of 450,000 different entries

Result Table C

Column_A = ¿Exists the account number in other register?
Column_B = if(NumberOfMatches > 1) //this means that the account be found 
in other user AND i want to get the first value of all posibles number of matches

                         |Account exists in other user|Match in User..
-----------------------------------------------------------------------------
| Name   | ID  | Account | Column_A | NumberOfMatches | Column_B    | BadID |
--------------------------------------------------------------------|-------|
| Nicole | 01  | 12345   | No       |       1         | Nicole (OK) | null  |
| Alexis | 02  | 67890   | Yes      |       2         | Nicole (BAD)|   01  |
-----------------------------------------------------------------------------

Thanks and regards!

Note: sorry for my english, im learning :p

Best Answer

SELECT DISTINCT
       CASE WHEN t2.ID IS NULL 
            THEN t1.Name
            ELSE t2.Name
            END Name,
       CASE WHEN t2.ID IS NULL 
            THEN t1.ID
            ELSE t2.ID
            END ID,
       CASE WHEN t2.ID IS NULL 
            THEN t1.Account
            ELSE t2.Account
            END Account,
       CASE WHEN t2.ID IS NULL 
            THEN 'No'
            ELSE 'Yes'
            END Column_A,
       ( SELECT COUNT(t4.Account)
         FROM BankAccounts t4
         WHERE t1.Account = t4.Account ) NumberOfMatches,
       CASE WHEN t2.ID IS NULL 
            THEN CONCAT(t1.Name, ' (OK)')
            ELSE CONCAT(t1.Name, ' (BAD)')
            END Column_B,
       CASE WHEN t2.ID IS NULL 
            THEN NULL
            ELSE t1.ID
            END BadID
FROM BankAccounts t1
LEFT JOIN BankAccounts t2 ON t1.Account = t2.Account
                         AND t1.ID < t2.ID
LEFT JOIN BankAccounts t3 ON t1.Account = t3.Account
                         AND t1.ID > t3.ID
WHERE t3.ID IS NULL
ORDER BY Account, ID;

fiddle

Related Solutions

Mysql – Finding Duplicates in LEFT JOIN

This may appear extrememly convoluted by here is the query you need

select * from
(select count(1) dupcount,emp_name,dept_name from table_a group by emp_name,dept_name) as a
left join
(select count(1) dupcount,emp_name,dept_name from table_b group by emp_name,dept_name) as b
on a.emp_name = b.emp_name and a.dept_name = b.dept_name where a.dupcount <> IFNULL(b.dupcount,0);

I loaded you sample data and I got this:

mysql> select * from
    -> (select count(1) dupcount,emp_name,dept_name from table_a group by emp_name,dept_name) as a
    -> left join
    -> (select count(1) dupcount,emp_name,dept_name from table_b group by emp_name,dept_name) as b
    -> on a.emp_name = b.emp_name and a.dept_name = b.dept_name where a.dupcount <> IFNULL(b.dupcount,0);
+----------+----------+------------------------+----------+----------+-----------+
| dupcount | emp_name | dept_name              | dupcount | emp_name | dept_name |
+----------+----------+------------------------+----------+----------+-----------+
|        2 | amar     | finance                |        1 | amar     | finance   |
|        1 | anthony  | information_technology |     NULL | NULL     | NULL      |
+----------+----------+------------------------+----------+----------+-----------+
2 rows in set (0.02 sec)

mysql>

Here is it without the dupcount values

mysql> select a.emp_name,a.dept_name,b.emp_name,b.dept_name from
    -> (select count(1) dupcount,emp_name,dept_name from table_a group by emp_name,dept_name) as a
    -> left join
    -> (select count(1) dupcount,emp_name,dept_name from table_b group by emp_name,dept_name) as b
    -> on a.emp_name = b.emp_name and a.dept_name = b.dept_name where a.dupcount <> IFNULL(b.dupcount,0);
+----------+------------------------+----------+-----------+
| emp_name | dept_name              | emp_name | dept_name |
+----------+------------------------+----------+-----------+
| amar     | finance                | amar     | finance   |
| anthony  | information_technology | NULL     | NULL      |
+----------+------------------------+----------+-----------+
2 rows in set (0.00 sec)

mysql>

Give it a Try !!!

Mysql – SQL Question: Selecting different language versions based on availability

you could choose a lot of ways. There is the one:

With tbl as (select distinct name from table)
Select coalesce(t1.name, t2.Name), coalesce(t1.field1, t2.fields) as field1....etc.
from tbl
 left join table t1 on tbl.NAME = t1.NAME and t1.lang = 'E'
 left join table t2 on tbl.NAME = t2.NAME and t2.lang = 'G'

Best Answer

Related Solutions

Mysql – Finding Duplicates in LEFT JOIN

Mysql – SQL Question: Selecting different language versions based on availability

Related Question