Help with select from 2 tables and insert into a third one 1

[Uneeeq]

Hi, I am a newbie hoping someone can help

I have a database called WEBSITE with about 1000 records
and the following tables
ACCOUNT containing tables account_id, account_email
account_id is integer, primary key, account_email is varchar
DOMAINS containing tables domain_id, domain_name, domain_account_id
domain_id is integer, domain_name is varchar and domain_account_id is integer
NEWDOMAINS containing tables domain_id, domain, owner
domain_id is integer, primary key, domain is varchar, owner is varchar



I am trying to populate NEWDOMAINS owner table with values from ACCOUNT account_email
NEWDOMAINS already has domain_id and domain tables populated which was a simple copy table task, my challenge is to bring in correct owner value which is contained in ACCOUNT into NEWDOMAINS

so the results should be
ACCOUNT account_id, account_email
| 77 | user@provider.com |
DOMAINS domain_id, domain_name, domain_account_id
| 873 | testdomain.com | 77 |

NEW DOMAINS domain_id, domain, owner
873 | testdomain.com | user@provider.com |



I have been trying to write something like this

select domain_account_id, domain_name from DOMAINS where domain eq domain_name
select account_email from ACCOUNT where domain_account_id eq account_id
insert account_email into USERS

to get the account_email in USERS populated for all 1000 records at once

I have worked a little in PHP but not in MYSQL can anyone help or point me to the right area of mysql manual?

Thanks in advance
Genie

 

[cyoung]

Give this a try

INSERT INTO account_email
SELECT domains.domain_account_id, domains.domain_name,
account.email LEFT JOIN account_id on domains.account_id =
account.account_id

 

[Uneeeq]

Thank you cyoung! You have sent me in the right direction and saved me hours of research/learning, I was able to populate the new table!