primary keys and auto increment

[leena]

Hi everyone.

I really need some help.

I am trying to create a table that has the following fields as primary:

leagueIndexID
playerID

Also I want playerID to auto increment. However, I want it to auto increment based on that leagueIndexID.

Eg)
leagueIndexID PlayerID
1 1
1 2
1 3
2 1
2 2
2 3

I have looked this up and I cannot get it.

I had these results:
leagueIndexID PlayerID
1 1
1 2
1 3
2 4
2 5
2 6

notice the playerID increments but has nothing to do with the leagueIndexID. This is not right.

I had something like this:
CREATE TABLE 1newITPlayer (
LeagueIndexID float(11,0) NOT NULL default '0',
PlayerID double(16,0) NOT NULL auto_increment,
PlayerNum float NOT NULL default '0',
PRIMARY KEY (PlayerID,LeagueIndexID)
) TYPE=MyISAM;

Please help me. Thank you for your time. Any help is greatly appreciated.

[leena]

Hi everyone.

Sorry that last message didn't quite look like I wanted it to.
That first number under "leagueIndexID" is the leagueIndexID, the second number is the "playerID". It just looks like one number under the leagueIndexID, but it is actually 2 (first one for leagueIndexID, second for playerID).

Eg)
leagueIndexID PlayerID
1 1 (leagueIndexID is 1, playerID is 1)
1 2 (leagueIndexID is 1 playerID is 2)
1 3 (leagueIndexID is 1 playerID is 3)
2 1 (leagueIndexID is 2 playerID is 1)
2 2
2 3

I have looked this up and I cannot get it.

I had these results:
leagueIndexID PlayerID
1 1
1 2
1 3
2 4
2 5
2 6

[leena]

I got it.

Thanks anyways everyone.

[cgraz]

you should post the query you used so people who run across this post with the same problem know how to solve it.

Cgraz