title: Insert multiple records with join
body: <p>I need some help in figuring out how to insert more than one records in a table using a join (when the join returns more than one values). So here is the scenerio:</p>
  
  <p>Table A:
  <code>A_ID bigserial, Role Varchar(25), Description varchar(25)</code></p>
  
  <p>Table B: 
  <code>B_ID bigserial, Role Varchar(25), Code varchar(25)</code></p>
  
  <p><strong>Table A and B are connected with column Role.</strong></p>
  
  <p>Example Entries in Table_A:</p>
  
  <pre><code>1, A, Standard
  2, B , Test
  3, C, Test
  4, D, Standard
  </code></pre>
  
  <p>Example Entries in Table_B:</p>
  
  <pre><code>1, A, ABC
  2, B, XYZ
  3, C, XYZ
  4, D, ABC
  </code></pre>
  
  <p>Basically what I need to do is check for Roles where description = Test, then insert entry for this Custom Role to Table_B with Code = ABC (If entry doesn't exist already)</p>
  
  <p>The following query will give me all the Test description Roles which do not have any entry with Code = ABC in table B</p>
  
  <p>Query1:</p>
  
  <pre><code>SELECT ROLE FROM TABLE_A A
  INNER JOIN TABLE_B B
  ON A.ROLE=B.ROLE
  WHERE A.Description ='Test'
  AND  B.CODE&lt;&gt;'ABC';
  </code></pre>
  
  <p>I have the following insert query:</p>
  
  <pre><code>insert into Table_B (Role , Code)
  select  (SELECT ROLE FROM TABLE_A A
  INNER JOIN TABLE_B B
  ON A.ROLE=B.ROLE WHERE A.Description ='Test'AND  B.CODE&lt;&gt;'ABC'), 'ABC';
  </code></pre>
  
  <p>The above insert query only works when Query1 returns one role, however I am not sure how to insert into table_A when Query1 returns more than 1 results.</p>
  
  <p>Can someone pls help? Not looking to use Stored Procs for the same</p>
  
  <p>Thanks.</p>
  
  <p>Edited:</p>
  
  <p>Example Entries in Table_A:</p>
  
  <pre><code>1, A, Standard
  2, B , Test
  3, C, Test
  4, D, Standard
  5, E, TEST
  </code></pre>
  
  <p>Example Entries in Table_B:</p>
  
  <pre><code>1, A, ABC
  2, B, XYZ
  3, B, ABC
  4, C, DEF
  5, C, XYZ
  6, D, ABC
  7, E, XYZ
  8, E, LLL
  </code></pre>
  
  <p>Query1 will not work here:</p>
  
  <pre><code>SELECT ROLE FROM TABLE_A A
  INNER JOIN TABLE_B B
  ON A.ROLE=B.ROLE
  WHERE A.Description ='Test'
  AND  B.CODE&lt;&gt;'ABC';
  </code></pre>
  
  <p>Using this query now:</p>
  
  <pre><code>SELECT distinct ROLE FROM TB  where role not in (
  SELECT B.ROLE FROM TA A
  INNER JOIN TB B
  ON A.ROLE=B.ROLE
  WHERE A.Description =Test
  AND  B.CODE=ABC)
  and role in (select role from TA where Description =Test);
  </code></pre>
  
  <p>How will the insert work now?</p>