On Apr 3, 2011, at 7:50 AM, Luuk wrote: > On 03-04-2011 14:43, Colin Cuthbert wrote: >> First time I've used this (or any!) mailing list, so sorry if I've done >> something wrong. >> >> Pretty sure my question (in the subect) is phrased badly but it's the best I >> could do! >> >> create table People(id integer primary key, name text); >> insert into People (name) values ('bob'); >> insert into People (name) values ('fred'); >> >> create table Cars(id integer primary key, name text); >> insert into Cars (name) values ('ford'); >> insert into Cars (name) values ('volvo'); >> >> create table CarOwners(id integer primary key, carId integer references >> Cars(id), ownerId integer references People(id)); >> insert into CarOwners (carId, ownerId) select Cars.id, People.id from Cars, >> People where Cars.name='ford' and People.name='bob'; >> >> create table Couples(id integer primary key, personId1 integer references >> People(id), personId2 integer references People(id)); >> >> The last 'insert' statement seems to work for inserting a row into the >> 'CarOwners' table, but I'm not sure that's the right/best way to do it. >> >> But how can I do a similar insert into the 'Couples' table? ie, how can I >> insert a row (specifying 'personId1' and 'personId2' via queries based on >> 'People.name') into the 'Couples' table? > > You forgot to define 'Couples'..... >> Does it start something like this? >> >> insert into Couples (personId1, personId2) select id, id from People where... > > select id, id from People will return the same id (from the same record) > twice.... > > somehting like: > select a.id, b.id from People a join People b on a.id<>b.id > will give other results, but what youactually want to be returned > depends on the definition of a 'Couple'... >
You also want to do all of the above in a TRANSACTION, preferably with a TRIGGER, to ensure the correct relationships are preserved. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users