Partially merge 2 tables

[wonderer]

hello all :)

i'm quite newbie so please don't kick me if this question can look stupid :P

i am needing to populate a table copying datas from other tables

i try to explain :)

i have tables A, B, C
C is empty

structure of table A is
call_id, name, birthday, phone, message

structure of table B is
name_id, name

table C should be
name_id, call_id, message

so i have to take 3 fields from table A substituting one of them (name) with corrispondant name_id form table B

i tried like this
[code]
$q = "SELECT B.name_id FROM B , A WHERE B.name = A.name";
$query="INSERT INTO C (name_id, call_id, message) SELECT $q, call_id, message FROM A ORDER by call_id";[/code]

obviously it doesn't work [img src=\"style_emoticons/[#EMO_DIR#]/unsure.gif\" style=\"vertical-align:middle\" emoid=\":unsure:\" border=\"0\" alt=\"unsure.gif\" /]

any idea of how to make it work?
thanks :)

[fenway]

Sounds like you want the following (UNTESTED):

[code]INSERT INTO C (name_id, call_id, message) SELECT B.name_id, A.call_id, A.message FROM A JOIN B ON ( B.name = A.name )[/code]

[wonderer]

[!--quoteo(post=377962:date=May 29 2006, 03:24 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ May 29 2006, 03:24 AM) [snapback]377962[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Sounds like you want the following (UNTESTED):

[code]INSERT INTO C (name_id, call_id, message) SELECT B.name_id, A.call_id, A.message FROM A JOIN B ON ( B.name = A.name )[/code]
[/quote]


thanks i'm gonna try and let u know :)