title: Remove Records from LEFT JOIN Only If Duplicates and Fits Certain Criteria
body: <p>I have a fairly big file that I matcedh with another file before uploading it to my database using MySQL. The original file was ~211k (<code>t1</code>) and the returning match after matching it with the existing database (<code>t2</code>) is around 300k -- which means I have to do almost 90k work of record-removal before I can upload.</p>
  
  <p>Since the first query where I used a <code>LEFT JOIN</code> to match them on name took so long, I saved the results as a new table called <code>matchnew</code> (the 300k records, seemingly with 90k of duplicates or bad matches). Here's a sample of the <code>matchnew</code> schema after I joined <code>t1</code> and <code>t2</code>:</p>
  
  <pre><code>CREATE TABLE `rnmatchnew` (
    `id1` varchar(255) DEFAULT NULL,
    `first1` varchar(255) DEFAULT NULL,
    `last1` varchar(255) DEFAULT NULL,
    `phone1` varchar(255) DEFAULT NULL,
    `zip1` varchar(255) DEFAULT NULL
    `id2` varchar(255) DEFAULT NULL,
    `first2` varchar(255) DEFAULT NULL,
    `last2` varchar(255) DEFAULT NULL,
    `phone2` varchar(255) DEFAULT NULL,
    `zip2` varchar(255) DEFAULT NULL;
  </code></pre>
  
  <p>(And the two IDs [<code>id1</code> and <code>id2</code>] do not match -- they're two unique identifiers from two different databases.)</p>
  
  <p>Right now I'm looking at most of those duplicates or bad matches by using this simple query:</p>
  
  <pre><code>SELECT *, COUNT(id1)
  FROM matchnew
  GROUP BY id1
  HAVING COUNT(id1) &gt; 1;
  </code></pre>
  
  <p>The good thing about each table that I matched had different unique identifiers attached to them (<code>id1</code> from the first table and <code>id2</code> from the second table, which now both exist in <code>matchnew</code>) -- so it should be fairly easy to see when records are appearing multiple times. Also I because I left joined two existing tables together to get <code>matchnew</code>, that means that I have two sets of data for each person from each table -- so two names, two phone numbers, two addresses, etc. But I only did the <code>LEFT JOIN</code> on first and last name to ensure I'd get the biggest possible return to make sure I didn't miss anybody in case they moved or we have different phone numbers for them, etc.</p>
  
  <p>My question is: Is there code I can write or add to the above query which will remove rows if they fit a certain criteria <em>only if</em> there is more than one unique ID in the table? So for example, if my <code>id1</code> was 1234567 and my query above showed that there were now three of me in the final column, is there additional code I can write to remove one or two (but not all three) of the duplicates or bad matches if my data doesn't match up with other qualifiers (e.g. phone number or zip code)?</p>
  
  <p>To further clarify, if my record with <code>id1</code>: 1234567 from the initial <code>t1</code> matched with three people with my name from <code>t2</code> -- is there a way to remove up to two of the rows if, for example, the record from <code>t1</code> matched the same phone number as one of the three records with the same name from <code>t2</code>? (The only reason why I specify "up to two" is because this example has three duplicates -- and if none of them match the phone number, I don't want to lose them all entirely in case that's a decision I can make manually.)</p>
  
  <p>That was way more complicated to describe than I expected -- so please just let me know if I can provide any further clarification! Thanks so much for the help.</p>