0

¡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

  • 1
    No need (ever) to apologize for your english. Are you using MySQL-8.0 or MariaDB-10.2 or newer? – danblack Feb 20 at 5:49
  • Thks man! im currently using MySQL-8.0 @danblack – Bryann Luna Feb 20 at 5:59
  • What output do you want if the amount of Account duplicates is 3 or more? – Akina Feb 20 at 6:37
  • @Akina the first value of all matches – Bryann Luna Feb 20 at 7:04
  • the first value of all matches This is for Column_B. But what about column Name - all except first? like in my answer? – Akina Feb 20 at 7:06
0
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

|improve this answer|||||
  • 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 – Bryann Luna Feb 20 at 7:59
  • I forgot change type 'INT' for 'Double' – Bryann Luna Feb 20 at 8:01
  • @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.. – Akina Feb 20 at 8:04
  • sorry sorry, here its the correct link – Bryann Luna Feb 20 at 8:05
  • In simple words, i should detect than the account bank of Nicole (12345) exists in other user – Bryann Luna Feb 20 at 8:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.