[SOLVED] INSERT into table every ID from another table?

[josborne]

I have a table that I need to insert a row for every ID in another table.

 

Essentially, at regular intervals I need to start a new "round". When this occurs, the "score" table needs to be populated with a row for each player.

 

The tables:

 

CREATE TABLE `Round_Score` (
`Round_Score_ID` int(11) NOT NULL auto_increment,
`Round_ID` int(11) NOT NULL default '0',
`Player_ID` int(11) NOT NULL default '0',
`Raw_Score` decimal(8,3) default NULL,
`Accuracy` decimal(7,4) default NULL,
`Round_Score` int(11) NOT NULL default '0',
PRIMARY KEY  (`Round_Score_ID`),
UNIQUE KEY `Round_ID` (`Round_ID`,`Player_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1


CREATE TABLE `Player_tbl` (
`Player_ID` bigint(20) NOT NULL auto_increment,
`Player_Name` varchar(50) NOT NULL,
PRIMARY KEY  (`Player_ID`),
UNIQUE KEY `Player_Name` (`Player_Name`)
) ENGINE=MyISAM AUTO_INCREMENT=26 DEFAULT CHARSET=latin1

 

So, I am trying to insert every Player_ID from Player_tbl into Round_Score table as its own row.

 

Any help would be really appreciated.

[josborne]

Still haven't figured this one out.

 

I tried this with little expectation that it might work:

 

insert into Round_Score (`Round_Score_ID`,
		`Round_ID`,
		`Player_ID`,
		`Raw_Score`,
		`Accuracy`,
		`Round_Score`
    select 	'NULL', 
	'0',
	Player_ID, 
	'0',
	'0',
	'0'

    from Player_tbl
        left outer join Round_score on Player_tbl.Player_ID=Round_Score.Player_ID;

 

It obviously failed.

[fenway]

You're not closing your column list with a parent.

[josborne]

Thanks. That's a little embarrassing.

 

Once I got it working I realized that the join was going to insert a row for every ID in the player AND every row in the Round table.

 

I got rid of the join and it works exactly as needed. and is so simple I am a little further embarrassed.

 

This place has been a huge help.