sql shit

example.sql

/*
This is just an example of our current db and number of dups
*/

SELECT
    MobilePhone, count(MobilePhone)
FROM TerminalMemberTable
WHERE IsActive = 1 AND LastTransaction IS NOT NULL
GROUP BY MobilePhone
HAVING count(MobilePhone) > 1
ORDER BY count(MobilePhone) DESC

/*
512xxxxxxx	4
...
5125555555	3   // Bob Anderson
 */

/*
So...

For row in rows
  Any other rows with row[MobilePhone]?
  Consolidate user information from those rows
  
  Any other rows with row[email]?
  Consolidate user information from those rows

I now should have all user information available and the card numbers that user has.
*/

select MemberCardNumber, MemberName, MemberSurname, MobilePhone, HomePhone, EmailAddress
from TerminalMemberTable
where IsActive = 1 and LastTransaction is not NULL
    AND TerminalMemberTable.MobilePhone = '5125555555'

/*
99999990005278    bob    anderson    5125555555                  [email protected]
99999900136201    Bob    Anderson    5125555555    5125556666
99999999005269    BOB    ANDERSON    5125555555
 */

select MemberCardNumber, MemberName, MemberSurname, MobilePhone, HomePhone, EmailAddress
from TerminalMemberTable
where IsActive = 1 and LastTransaction is not NULL
      AND TerminalMemberTable.EmailAddress = '[email protected]'

/*
99999990005278    bob    anderson    5125555555                  [email protected]
99999990005561                                                   [email protected]
 */