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
> [email protected]
> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
