title: How to find which foreign keys are referenced
body: <p>I have two classes - <code>logical_interfaces</code> and <code>pop_vlans</code> with a one-to-one relationship.</p>
  
  <p>In the <code>logical_interfaces</code> table the foreign key is <code>vlan_id</code> which relates to the <code>id</code> column in <code>pop_vlans</code>.</p>
  
  <p>In my controller I get a list of <code>vlan_number</code>s (stored in <code>pop_vlans</code> like so:</p>
  
  <pre><code>@vlan_numbers = PopVlan.find_all_by_pop_id(@device.pop.id)
  </code></pre>
  
  <p>(<code>@device.pop.id</code> is the <code>pop</code> the <code>pop_vlan</code> belongs to which is the same as the <code>device</code>)</p>
  
  <p>then in my view I generate a select from this collection:</p>
  
  <pre><code>&lt;%= collection_select 'logical_interface', "vlan_id", @vlan_numbers, :id, :vlan_number %&gt;
  </code></pre>
  
  <p>This is all fine but I don't want to fetch <em>all</em> the <code>vlan_numbers</code>, only the ones whose <code>id</code> is not referenced in the <code>logical_interfaces</code> table. i.e. only those numbers which are not already in use.</p>
  
  <p>I am terribly sorry for the confusion as this is quite a long-winded question to ask something that is hopefully straight-forward.</p>
  
  <p>I think it could be done with a manual SQL query but I have no idea if there are built in methods to allow this to be done 'the Rails way'.</p>
  
  <p>I think the MySQL to achieve the opposite would be <code>SELECT pop_vlans.id, vlan_number FROM pop_vlans 
  LEFT JOIN logical_interfaces ON logical_interfaces.id = pop_vlans.id</code> but I can't actually think how to adjust that query to find <code>vlan_numbers</code> that don't match.</p>
  
  <p><strong>TLDR</strong></p>
  
  <pre><code>logical_interfaces | pop_vlans
  -------------------|-----------
       vlan_id-------|-----&gt;id
         ....        |  vlan_number
  </code></pre>
  
  <p>get all from the right table whose foreign key is not referenced in the left table</p>
  
  <p>I am thinking that it might just be a lot easier to add a new column to the table to act as a flag if it's in use and use that as the condition in the query.</p>