title: Duplicate row in multi table join
body: <p>I have four tables as below:</p>
  
  <pre><code>tenant
  +----+------------+
  | id | name       |
  +----+------------+
  |  1 | John Smith | 
  |  2 | Anna Jones | 
  +----+------------+
  
  property
  +----+-------------+---------------+
  | id | landlord_id | address       |
  +----+-------------+---------------+
  |  1 |           1 | King Street 1 | 
  |  2 |           1 | Green Grove 2 | 
  |  3 |           2 | Queen Stree 3 | 
  +----+-------------+---------------+
  
  tenant_has_property
  +-----------+-------------+
  | tenant_id | property_id |
  +-----------+-------------+
  |         1 |           1 | 
  |         1 |           2 | 
  +-----------+-------------+
  
  landlord
  +----+-----------------+
  | id | name            |
  +----+-----------------+
  |  1 | Best Homes Ltd. | 
  |  2 | RealEstates Inc | 
  +----+-----------------+
  </code></pre>
  
  <p>Now I would like to get a list of all tenants that rent a property from landlord id = 1</p>
  
  <p>When I run a query like below:</p>
  
  <pre><code>SELECT 
    tenant.id, tenant.name 
  FROM 
    tenant, property, tenant_has_property 
  WHERE 
    tenant.id = tenant_has_property.tenant_id AND 
    tenant_has_property.property_id = property.id AND 
    property.landlord_id = 1
  </code></pre>
  
  <p>I am getting duplicate rows:</p>
  
  <pre><code>+----+------------+
  | id | name       |
  +----+------------+
  |  1 | John Smith | 
  |  1 | John Smith | 
  +----+------------+
  </code></pre>
  
  <p>I know changing the query to</p>
  
  <pre><code>SELECT 
    DISTINCT tenant.id, tenant.name ...
  </code></pre>
  
  <p>will remove duplicate rows, but my question is:</p>
  
  <p>Is it possible to avoid using DISTINCT and construct JOINs in such a way that no duplicate rows will be returned? Already tried all combinations of INNER, LEFT JOINS without much luck :(</p>
  
  <p>Any suggestions very much appreciated</p>