title: MySQL joining data from 2 tables based on foreign key in a third, without duplicates or group by
body: <p>I have three tables that look like this:</p>
  
  <pre><code>People:
  +------------+-------------+------+-----+-------------------+----------------+
  | Field      | Type        | Null | Key | Default           | Extra          |
  +------------+-------------+------+-----+-------------------+----------------+
  | id         | int(11)     | NO   | PRI | NULL              | auto_increment |
  | fname      | varchar(32) | NO   |     | NULL              |                |
  | lname      | varchar(32) | NO   |     | NULL              |                |
  | dob        | date        | NO   |     | 0000-00-00        |                |
  | license_no | varchar(24) | NO   |     | NULL              |                |
  | date_added | timestamp   | NO   |     | CURRENT_TIMESTAMP |                |
  | status     | varchar(8)  | NO   |     | Allow             |                |
  +------------+-------------+------+-----+-------------------+----------------+
  
  Units:
  +----------+-------------+------+-----+---------+----------------+
  | Field    | Type        | Null | Key | Default | Extra          |
  +----------+-------------+------+-----+---------+----------------+
  | id       | int(11)     | NO   | PRI | NULL    | auto_increment |
  | number   | varchar(3)  | NO   |     | NULL    |                |
  | resident | int(11)     | NO   | MUL | NULL    |                |
  | type     | varchar(16) | NO   |     | NULL    |                |
  +----------+-------------+------+-----+---------+----------------+
  
  Visits:
  +----------+-----------+------+-----+---------------------+----------------+
  | Field    | Type      | Null | Key | Default             | Extra          |
  +----------+-----------+------+-----+---------------------+----------------+
  | id       | int(11)   | NO   | PRI | NULL                | auto_increment |
  | vis_id   | int(11)   | NO   | MUL | NULL                |                |
  | unit     | int(11)   | NO   | MUL | NULL                |                |
  | time_in  | timestamp | NO   |     | CURRENT_TIMESTAMP   |                |
  | time_out | timestamp | NO   |     | 0000-00-00 00:00:00 |                |
  +----------+-----------+------+-----+---------------------+----------------+
  </code></pre>
  
  <p>There are multiple foreign keys linking these tables:</p>
  
  <pre><code>units.resident -&gt; people.id
  
  visits.unit    -&gt; units.id
  
  visits.vis_id  -&gt; people.id
  </code></pre>
  
  <p>I am able to run this query to find all <i>residents</i> ie - everyone from <code>people</code> that are referenced by the <code>units.resident</code> foreign key:</p>
  
  <pre><code>SELECT  concat(p.lname, ', ', p.fname) as 'Resident', p.dob as 'Birthday', 
  u.number as 'Unit #' 
  from people p, units u
  where p.id = u.resident
  order by u.number
  </code></pre>
  
  <p>It returns the results I want... However, it'd be useful to do the <i>opposite</i> of this to find all the people who <i>are not</i> residents ie- everyone from <code>people</code> who aren't referenced by the <code>units.resident</code> foreign key.</p>
  
  <p>I've tried many different queries, most notably some inner and left joins, but I'm getting waaaaay too many duplicate entries (from what I've read here, this is normal). The only thing I've found that works is using a <code>group by license_no</code>, because as of now the "residents" don't have this information, like this:</p>
  
  <pre><code>SELECT  p.id, concat(p.lname, ', ', p.fname) as 'Visitor',
  p.license_no as 'License', u.number from people p
  left join units u on u.number &lt;&gt; p.id
  group by p.license_no order by p.id;
  </code></pre>
  
  <p>This works for all but <i>one</i> resident, who's <code>u.number</code> is displayed on ALL results. The residents will soon have <code>license_no</code> entries, and I can't have that one odd entry in the returned results all the time, so this query won't work as a long-term solution.</p>
  
  <p>How can I structure a query <i>without</i> a <code>group by</code> that will return the results I want?</p>