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'...
;)
> Thanks.
>
> Colin.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
