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
