title: PostgreSQL; Using a SELECT sub-query in an INSERT
body: <p>I am trying to use a <code>SELECT</code> sub-query to select all <code>staff</code> users who have the attribute <code>Likes tea</code> and then for all other staff users insert into the table a row for each user with the attribute saying that they also like tea (it turns out everyone likes tea but that wasn't recorded when the table data was gathered).</p>
  
  <p>This is an example of the data set;</p>
  
  <pre><code> tbl_users:
   user | attribute     
  ------+---------------
   dave | Likes tea     
   dave | Likes coffee  
   alan | Likes tea     
   fred | Likes juice   
   fred | Likes water   
   john | Likes cola    
  
   tbl_groups:
   user | group
  ------+---------------
   dave | staff         
   alan | staff         
   fred | staff         
   john | staff         
   neil | non-staff     
   tony | non-staff     
  </code></pre>
  
  <p>Below are the missing statements that would achieve the same result if I were to write out individually all the required <code>INSERT</code> statements, however there are thousands of <code>INSERT</code>s required in the real data set so I really want to use a sub-query (or similar) to do it for me:</p>
  
  <pre><code>INSERT INTO tbl_users (user, attribute) VALUES ('fred', 'Likes tea');
  INSERT INTO tbl_users (user, attribute) VALUES ('john', 'Likes tea');
  </code></pre>
  
  <p>This is the closest I have come to selecting all the users (not creating the required <code>INSERT</code>s, just selecting them to ensure I have the correct user list):</p>
  
  <pre><code>SELECT DISTINCT(tu.username) FROM tbl_users tu LEFT JOIN 
  
      (
       SELECT u.username FROM tbl_users u INNER JOIN tbl_groups g
       ON (u.username=g.username)
       WHERE g.groupname = 'staff' and u.attribute = 'Likes tea'
       ) AS ts
  
  ON tu.username=ts.username
  WHERE tu.username!=ts.username ORDER BY tu.username;
  </code></pre>
  
  <p>The problem is that this is returning every user listed in tbl_users. I think the problem is with the <code>LEFT JOIN</code> to the sub-query, however running that sub-query on its own sucessfully returns only the list of users who like tea, so I was expecting this part to then select the inverse user set from tbl_users (all users without the 'Likes tea' attribute) <code>WHERE tu.username!=ts.username ORDER BY tu.username;</code></p>
  
  <p>What's going on here, is the ON clause overiding the WHERE clause?</p>
  
  <p><strong>EDIT</strong>:</p>
  
  <p>This is a pseudo example of what I want to do:</p>
  
  <p>The sub-query is selecting all the users in my specified group (staff) with the attrbiute already defined, so I need to use that user list in some sort of <code>NOT IN</code> fassion against the entire users list:</p>
  
  <pre><code>for each (username from list of all users)
    if username NOT IN (sub-query of users who already have the attribute)
    then
      add attribute to this user
    fi
  next
  </code></pre>