INSERTING VALUES FROM OTHER 2 TABLES

[Anon]

Hi,
How we can insert the the values[which is from other 2 tables] to a new table with out using the temporary variables. anybody can help me for that sql query.
thanks in advance
bye
nsj

[trippyd]

Build up the select statement with the join to return the values you want:

select something, otherthing from othertable, sometable where othertable.id = sometable.id

then use:

insert into tablename (col1, col2) select something, otherthing from othertable, sometable where othertable.id = sometable.id

Should work

[Anon]

David,
thanks for u'r reply. I want to insert the values which r getting from the two tables[t1,t2] to the third table which is t3. for that that query is enough?

[trippyd]

That query should work, if I had a better idea of the table structure and what you are trying to do, I could whip up a quick query for you.

nsj wrote:

David,
thanks for u'r reply. I want to insert the values which r getting from the two tables[t1,t2] to the third table which is t3. for that that query is enough?

[Anon]

david,
once again thanks. check this.
1. the empid from the emp table based on autono
2. the id,firstname,lastname,state,zip.... from the consu based on id
3. insert id,empid,firstname,lastname,state,zip with above values in the job table.
i want insert these datas with one sqlquery with out using the temporary variable.

and i am using mysql database also.

[trippyd]

ok, something like this ought to work:

insert into job (id, empid, firstname, lastname, state, zip) select id, empid, firstname, lastname, state, zip from consu, emp where empid = SOMEVALUE and id = SOMEOTHERVALUE;

Ideally, you would probably want a relationship (foreign key) between the emp table and the consu table. Since you already have a primary key in both tables, I would just have emp.empid = consu.id, then the sql would look like this:

insert into job (id, empid, firstname, lastname, state, zip) select id, empid, firstname, lastname, state, zip from consu, emp where empid = id and id = SOMEVALUE;

if they are different, then just stick with the first SQL.

D

nsj wrote:

david,
once again thanks. check this.
1. the empid from the emp table based on autono
2. the id,firstname,lastname,state,zip.... from the consu based on id
3. insert id,empid,firstname,lastname,state,zip with above values in the job table.
i want insert these datas with one sqlquery with out using the temporary variable.

and i am using mysql database also.

[Anon]

Thanks a lot David, It is working perfectly.
nsj