-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: Monday, March 03, 2008 4:17 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Can I automatically create a 'disambiguation' number in a second field of a primary key?
Paul Hilton wrote: > Hello, > > I have source tables Talker and Listener, each with fields ID (PK, Integer) > and Group (Integer): > > CREATE TABLE Talker (ID INTEGER, Group INTEGER, Primary Key (ID)); > Ditto Listener > > I would like to make a table Communications with fields Group (PK, Integer), > Slot(PK, Integer) TalkerID (Integer), ListenerID (Integer): > > CREATE TABLE Communications (Group INTEGER, Slot INTEGER, TalkerID INTEGER, > ListenerID INTEGER, PRIMARY KEY (Group, Slot)); > > I want all combinations of Talker and Listener where > Talker.Group=Listener.Group and Talker.ID!=ListenerID > > Here is the problem: I want Slot created to disambiguate the Primary Key, So > that for every value of Group the value of Slot starts at 1 and counts up. I > don't actually care which TalkerID / ListenerID which value of Slot > corresponds to. > > E.g. > INSERT INTO Communications (Group, TalkerID, ListenerID) > SELECT Talker.Group, Talker.ID, Listener.ID > FROM Talker, Listener > WHERE Talker.Group=Listener.Group AND Talker.ID!=Listener.ID; > > Tries to insert all the records, but doesn't make a Slot value to > disambiguate. > > A numerical illustration of what I want: > > Talker > ID Group > 11 1 > 22 1 > 34 1 > 47 2 > 15 2 > 37 2 > > Listener > ID Group > 11 1 > 12 1 > 22 1 > 47 2 > 15 2 > > Should Yield Communications > Group Slot TalkerID ListenerID > 1 1 11 12 > 1 2 11 22 > 1 3 22 11 > 1 4 22 12 > 1 5 34 11 > 1 6 34 12 > 1 7 34 22 > 2 1 47 15 > 2 2 15 47 > 2 3 37 47 > 2 4 37 15 > > Thanks for any help. > Paul Hilton > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > It's not quite the same but you could try this instead. create table talker (id integer primary key, group integer); create table listener (id integer primary key, group integer); create table communications (id integer primary key, group integer, talker integer, listener integer); After you insert your data into talker and listener. insert into talker ... insert into listener ... You run the following commands to build the communications table. delete from communications; insert into communications select null, t.group, t.id, l.id from talker as t join lister as l where l.group = t.group and l.id != t.id order by t.group, t.id, l.id; This should produce the following table with a unique unambiguous id for each communication. id group talker listener 1 1 11 12 2 1 11 22 3 1 22 11 4 1 22 12 5 1 34 11 6 1 34 12 7 1 34 22 8 2 15 47 9 2 37 15 10 2 37 47 11 2 47 15 HTH Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Thanks for the suggestion, However it doesn't solve my problem, perhaps because of something I failed to say. I am intending to use these 'Slot' numbers to schedule experiments between talkers and listeners. The 'Slot' refers to a time slot. I would like the experiments within each group to go on simultaneously. So the first experiment in group 1 occurs in the same time slot as the first experiment in all the other groups. Paul Hilton _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
