Copy usernames into another table

Some are already there, some aren't

[s9901470]

I have a table1 with usernames and passwords, and another table2 I want to link it to using usernames.

But table2 only has some of the usernames, not others. I want to find out which ones, and insert them all into table2.

Could anyone recommend a snippet of code that would do this? I think it might involve joining both tables, then finding out which do not match?

many thanks

[coopster]

If your database allow you to run subqueries you should be able to

INSERT INTO table2 
 (username) 
 SELECT table1.username 
 FROM table1 
 LEFT JOIN table2 ON table1.username = table2.username 
 WHERE table2.username IS NULL 
;

As always, make backups or run these tests on duplicated tables first so you don't end up muffing your LIVE data!

[phparion]

do you know a good tutorial on joins?

[s9901470]

That works great, many thanks. How would I modify that to insert two values, e.g. the username and the id number.
I tried using VALUES as below but it didn't work.

NSERT INTO g2( username,wave )
VALUES username,id
SELECT users_1.username
FROM users_1
LEFT JOIN g2 ON users_1.username = g2.username
WHERE g2.username IS NULL

[coopster]

Almost, s9901470. Except the SELECT clause is the VALUES list you are trying to use. Try this:

INSERT INTO g2 
 (username, wave) 
 SELECT 
  users_1.username, 
  users_1.id 
 FROM users_1 
 LEFT JOIN g2 ON users_1.username = g2.username 
 WHERE g2.username IS NULL 
;

---

do you know a good tutorial on joins?

Here are a couple of basics that may be helpful:

LEFT JOIN [webmasterworld.com]
INNER JOIN [webmasterworld.com]