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

Question

¡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

• danblack: No need (ever) to apologize for your english. Are you using MySQL-8.0 or MariaDB-10.2 or newer?
• user186910: Thks man! im currently using MySQL-8.0 @danblack
• Akina: What output do you want if the amount of Account duplicates is 3 or more?
• user186910: @Akina the first value of all matches
• Akina: the first value of all matches This is for Column_B. But what about column Name - all except first? like in my answer?

Answer 1

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

• user186910: Nice tool! I didn't know about his existence! Well, i've designed one case in that tool (Tables), as you can see, we can have one table in where we have employees with "correct" accounts (Table Employees) and other table than can contains same information BUT in accounts bank we can find variations (This problem come from business and i cant change it), so, my idea was that using left join query with 'On a.Account Like concat('%', b.Account, '%') try to identify the cases in the table than you designed link
• user186910: I forgot change type 'INT' for 'Double'
• Akina: @BryannLuna Edit your fiddle. And check that ALL statements are executed without errors (in your fiddle 5th query, INSERT INTO, gives an error). Put each query in a separate field (error messages are listed only for 1st query in the inputbox) until their debugging, and compact into one field only when they have no errors..
• user186910: sorry sorry, here its the correct link
• user186910: In simple words, i should detect than the account bank of Nicole (12345) exists in other user