title: SQL LEFT JOIN on two possible columns
body: <p>We are adding a table to our database schema. It has a relationship to an already existing table, which we added a foreign key for. Mind you, I didn't create this schema nor do I have permission to change much. The application has been running for a while and they are hesitant to change much.</p>
  
  <p>USER_ACTIVITY_T (preexisint table - only relevant columns referred)</p>
  
  <ul>
  <li>activity_id (pk) </li>
  <li>username</li>
  <li>machineid (fk - recently added)</li>
  </ul>
  
  <p>MACHINE_T (new table)</p>
  
  <ul>
  <li>machineid (pk - auto increment)</li>
  <li>machinename (unique)</li>
  </ul>
  
  <p>From the point where I added the machine table, it collects machine data; allowing users to see what machines were involved during the activity. This is useful but it only shows data from the point that it was implemented. A lead asked me to attempt to fill preexisting records by referring to the username associated with the machine. We understand that this is not 100% accurate but... yeah. Our idea was to add username to MACHINE_T and use as a way to populate the machinename in reports retroactively (which assumes that the user has only used one machine and never changed their username).</p>
  
  <p>So, the new MACHINE_T table would look like:</p>
  
  <p>MACHINE_T (new table)</p>
  
  <ul>
  <li>machineid (pk - auto increment)</li>
  <li>machinename (unique)</li>
  <li>username</li>
  </ul>
  
  <p>Right now, our current SQL is:</p>
  
  <pre><code>SELECT * FROM `USER_ACTIVITY_T` LEFT JOIN `MACHINE_T` 
  ON MACHINE_T.machineid=USER_ACTIVITY_T.machineid
  </code></pre>
  
  <p>Anyone have any suggestions on how to join on the username if USER_ACTIVITY_T.machineid is null but has a matching username? I'm sorry. This is an odd request that I may spend far too much time over-analyzing. Thank you for any help. I'm almost tempted to just say it can be reasonably done.</p>