title: mysql select data from archive table if not in current table
body: <p>Not sure if the title explains my situation good enough but I've searched and found that I can do a WHERE NOT EXISTS clause, but I just can't seem to figure out exactly how it needs to be structured. Below is my situation.</p>
  
  <p>I have a table <strong>agents</strong>, that contains agent login info etc.</p>
  
  <p>I have another table <strong>listings</strong>, that is linked to the agents table with "agent_id".</p>
  
  <p>Agents have their own logins and can add their listings etc, and the whole system pulls their listings based on who is logged in etc.</p>
  
  <p>I ran into the situation where the super admin now wants to be able to delete an agent, but they want their old listings to remain archived and searchable etc in the database.</p>
  
  <p>I created a table <strong>agents_archived</strong>, and when the super admin removes them it copies their row in <strong>agents</strong> to <strong>agents_archived</strong> and removes it from the agents table.</p>
  
  <p>My issue is that I'm not sure how to have the query select their agent data if it doesn't exist in the current agents table. For instance here is a search query.</p>
  
  <pre><code>SELECT l.*, a.first_name, a.last_name FROM listings l 
  LEFT JOIN agents a ON l.agent_id = a.id WHERE l.agent_id = '" . $_GET['qa'] . "' 
  ORDER BY FIELD(l.status, 'active','inactive','under-contract','on-hold','title-issue','auction','closed','withdrawn'), l.street_name
  </code></pre>
  
  <p>I need it to pull the agents columns from the agents_archived table if it doesn't exist in the current.</p>
  
  <p>I hope this makes sense.</p>