insert from two tables

cornercuttinjoe

im sure this is an easy question, but its been a while since i have done complicated sql. i dont quite remember how to do it.

basically, i have table a, b, and c.
say table a has attributes name, address, city, phone
say table b has attributes address, city
say table c has attributes phone, area_code

how could i do an insert where i can take a value of table b and a value of table c and put those in the corresponding columns in table a.

basically, how could i do this

insert into table a (address, phone) values ((select address from b where city='My City'), (select phone from c where area_code = 111));

better yet, how could i get the input from one table and just input a static value for the second, like this...

insert into table a (address, phone) values ((select address from b where city='My City'), '$my_phone_num');

do you see what I am trying to do? i am wanting to use data from two different tables to insert into a third table.

i cant seem to remember this, and google hasnt helped any.

thanks in advance. i know its simple, im just not sure on the syntax.

inerte

YOU can use a JOIN but some rows need to be equalized otherwise you will get duplicated data. Are they on the same order? That is, the first row on table "b" should be put in table "a" with the first row from the table "c"?

Then you can do:

ALTER TABLE b ADD id INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ;
ALTER TABLE c ADD id INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ;

INSERT INTO a (address, city, phone) SELECT b.address, b.city, c.phone FROM b, c WHERE b.id = c.id;

If they don't share the same order, you must find columns to match:

INSERT INTO a (address, city, phone) SELECT b.address, b.city, c.phone FROM b, c WHERE b.some_field = c.some_field

Which weren't provided on your example "b" and "c" tables, so I hope you have them :p

About your second question, you can put "static" values. Just do:

INSERT INTO a (address, city, phone) SELECT b.address, 'something', '$my_phone_number' FROM b, c WHERE b.id = c.id;

Anyway you almost got it right, but the INSERT SELECT syntax doesn't have parenthesis after VALUES

cornercuttinjoe

supercool yo!

thanks a lot. that did the trick.

i didnt remember that you could just put the SELECT right after the col names that you wanted to fill. I thot you needed the VALUES word in there.

thanks a ton. That saves me from doing 2 queries. Now i can simplify it to 1.

I appreciate it.

take care