title: SQL: JOIN multiple tables with different size
body: <p>I am not someone who works a lot with database so the answer can be easy but I am unable to find it.
  Here is a simplified version of my database (which is in <strong>SQLITE</strong> btw):
  <a href="https://i.stack.imgur.com/RjOJZ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/RjOJZ.png" alt="database image"></a></p>
  
  <p>I have the following values in my database (I only put the id here because only them are relevant):</p>
  
  <ul>
  <li>device.id: 1, 2, 3</li>
  <li>capability.id: 10, 20, 30, 40, 50</li>
  <li>action.id: 100, 200, 300, 400, 500</li>
  <li>action.id_capability: 10, 30, 40, 10, 50</li>
  </ul>
  
  <p>And here are entries in map_device_cap:</p>
  
  <ul>
  <li>1, 10</li>
  <li>1, 30</li>
  <li>1, 40</li>
  <li>2, 10</li>
  <li>2, 20</li>
  <li>2, 50</li>
  <li>3, 20</li>
  </ul>
  
  <p>Entries in map_device_action:</p>
  
  <ul>
  <li>1, 100</li>
  <li>1, 200</li>
  <li>1, 300</li>
  <li>2, 400</li>
  <li>2, 500</li>
  </ul>
  
  <p>Now I want to create SELECT request that group tables map_device_cap and map_device_action. In this case the result will be:</p>
  
  <ul>
  <li>1, 10, 100</li>
  <li>1, 30, 200</li>
  <li>1, 40, 300</li>
  <li>2, 10, 400</li>
  <li>2, 20, null</li>
  <li>2, 50, 500</li>
  <li>3, 20, null</li>
  </ul>
  
  <p><strong>NOTE</strong>: the table map_device_cap will ALWAYS be larger than map_device_action.</p>
  
  <p>I have first try the following request:</p>
  
  <pre><code>SELECT map_device_cap.id_device, map_device_cap.id_capability,
         map_device_action.id_action 
  FROM map_device_cap NATURAL LEFT JOIN map_device_action
  </code></pre>
  
  <p>But this request gave me the following result:</p>
  
  <ul>
  <li>1, 10, 100</li>
  <li>1, 10, 200</li>
  <li>1, 10, 300</li>
  <li>1, 30, 100</li>
  <li>1, 30, 200</li>
  <li>1, 30, 300</li>
  <li>1, 40, 100</li>
  <li>1, 40, 200</li>
  <li>1, 40, 300</li>
  <li>2, 10, 400</li>
  <li>2, 10, 500</li>
  <li>2, 20, 400</li>
  <li>2, 20, 500</li>
  <li>2, 50, 400</li>
  <li>2, 50, 500</li>
  <li>3, 20, null</li>
  </ul>
  
  <p>As you can see there are duplicates entries and I don't know how to remove them.</p>
  
  <p>If anyone has an idea or need more information, please let me know.</p>
  
  <p>Regards</p>