PostgreSQL; Using a SELECT sub-query in an INSERT
<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>