Paul Hilton wrote:
>
> 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.
>
Then you need to get fancier. :-)
The following SQL shows how to use a trigger to update a separate table
that stores the slot values to be inserted into your communications
table. The trace at the end shows my results.
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, slot integer, talker integer, listener integer);
create table slots ("group" integer primary key, slot integer);
create trigger in_trig after insert on communications
begin
insert or replace into slots select new."group",
coalesce((select slot from slots where "group" = new."group") +
1, 1);
update communications
set slot = (select slot from slots where "group" = new."group")
where id = new.id;
end;
insert into talker values (11, 1);
insert into talker values (22, 1);
insert into talker values (34, 1);
insert into talker values (47, 2);
insert into talker values (15, 2);
insert into talker values (37, 2);
insert into listener values (11, 1);
insert into listener values (12, 1);
insert into listener values (22, 1);
insert into listener values (47, 2);
insert into listener values (15, 2);
delete from slots;
delete from communications;
insert into communications
select null, t."group", 0, t.id, l.id
from talker as t
join listener as l
where l."group" = t."group"
and l.id != t.id
order by t."group", t.id, l.id;
.mode column
.header on
select * from communications;
select slot, "group", talker, listener
from communications order by slot, "group";
SQLite version 3.5.6
sqlite> create table talker (id integer primary key, "group" integer);
snip...
sqlite> select * from communications;
id group slot talker listener
---------- ---------- ---------- ---------- ----------
1 1 1 11 12
2 1 2 11 22
3 1 3 22 11
4 1 4 22 12
5 1 5 34 11
6 1 6 34 12
7 1 7 34 22
8 2 1 15 47
9 2 2 37 15
10 2 3 37 47
11 2 4 47 15
sqlite> select slot, "group", talker, listener
...> from communications order by slot, "group";
slot group talker listener
---------- ---------- ---------- ----------
1 1 11 12
1 2 15 47
2 1 11 22
2 2 37 15
3 1 22 11
3 2 37 47
4 1 22 12
4 2 47 15
5 1 34 11
6 1 34 12
7 1 34 22
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
