title: sql join two tables and get the unique values from one table based on another
body: <p>I have the following <strong>table1</strong>, in which an <code>id</code> could appear multiple times and each time with a unique <code>name</code> and the same for <code>name</code>, it could appear multiple times and each time with a different <code>id</code>.</p>
  
  <pre><code>id  name
  ---------
  A2  B6
  A3  B2
  A3  B400
  A5  B100
  A7  B200
  A8  B300
  A8  B2
  A8  B3
  </code></pre>
  
  <p>and here is the <strong>table2</strong> in which all of the <code>id</code>s from <strong>table1</strong> exist, but not all of the <code>name</code>s. And each <code>id</code> has a <code>surname</code>.</p>
  
  <pre><code>id  name    surname
  -------------------
  A1          Lastname1
  A2          Lastname2
  A3  B1      Lastname3
  A4          Lastname4
  A5  B2      Lastname5
  A6          Lastname6
  A7  B3      Lastname7
  A8  B4      Lastname8
  A9          Lastname9
  A10 B6      Lastname10
  </code></pre>
  
  <p>here is the query result:
  - the first column: list of all of the unique <code>id</code>s from <strong>table1</strong> PLUS the list of all of the <code>name</code>s in <strong>table1</strong> that are not found in the <code>name</code> column of <strong>table2</strong>.
  - second column: the <code>surname</code> of the each element in <code>id</code> column of the results table taken from <strong>table2</strong>, all of the <code>id</code>s from <strong>table1</strong> have a corresponding surname, but if the <code>name</code> from the <strong>table1</strong> was not found in <strong>table2</strong>, then we put the same <code>name</code> as the surname.
  - third column: if <code>id</code> or <code>name</code> from the <strong>table1</strong> was found in the <strong>table2</strong>, then the status will be <strong>FOUND</strong>, however if the <code>name</code> from <strong>table1</strong> was not found, it will get <strong>NOT FOUND</strong></p>
  
  <pre><code>id      surname     status
  ---------------------------
  A2      Lastname2   FOUND
  A3      Lastname3   FOUND   
  A5      Lastname5   FOUND   
  A7      Lastname7   FOUND   
  A8      Lastname8   FOUND   
  A10     Lastname10  FOUND   
  B100    B100        NOT FOUND
  B200    B200        NOT FOUND
  B300    B300        NOT FOUND
  B400    B400        NOT FOUND
  </code></pre>