3

I am trying to create a many-many relationship between the max(id) in table1 with all the ids in table2, using a lookup table called table1_table2.

Ultimately so that the rows in table1_table2 will be:

table1_id, table2_id
30, 1
30, 2
30, 3
...
30, 10000

How can I do this?

I have tried 

insert into insert into table1_table2 (table1_id, table2_id) 
   values (select max(id) from table2, select id from table3);

and

insert into insert into table1_table2 (table1_id, table2_id) 
   select max(table1_id), table2_id from table1 
      join table1_table2 on table1_table2.table1_id = table1.id 
      outer join table1_table2 on table1_table2.table2_id = table2.id;

But neither seem to work

Abe
  • 12,956
  • 12
  • 51
  • 72
  • Can you add in the question where the data are now? In `table2`, `table3` or both ? – ypercubeᵀᴹ Sep 27 '11 at 16:23
  • the data are currently in both, I am trying to create a many-many relationship between the max(id) in table1 with all the ids in table2, using a lookup table called table1_table2. – Abe Sep 27 '11 at 16:33

2 Answers2

3

You have 3 tables, I see. I guess you mean this:

insert into table1_table2 (table1_id, table2_id) 
   SELECT
         (select max(id) from table2)            --- or perhaps: from table1 ?
       , id
   FROM table3                                   --- or perhaps: FROM table2 ?
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • worked perfectly, thanks. you are correct with the 'from table1 and from table2' rather than table2 and table3. Sorry that I changed the question. – Abe Sep 27 '11 at 16:35
3

It sounds like this is what you want:

INSERT INTO table1_table2 (table1_id, table2_id) 
    SELECT MAX(table1.id), table2.id FROM table1, table2 GROUP BY table2.id;
daiscog
  • 11,441
  • 6
  • 50
  • 62
  • worked perfectly, thanks. helpful use of 'group by' to avoid duplicates (even though I did not specify this, it is the primary key, so there would be none). – Abe Sep 27 '11 at 16:31