> Date: Sun, 3 Apr 2011 14:50:30 +0200
> From: luu...@gmail.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Question:how to insert row with multiple values from
> same field of different rows of another table?
>
> 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'...
>
> ;)
Sorry for not being clear.
The row I want to insert into the Couples table would contain, for example:
personId1 = bob's id (ie the 'id' member of the 'People'-table-row whose 'name'
member is 'bob')
personId2 = fred's id (ie the 'id' member of the 'People'-table-row whose
'name' member is 'fred')
So what I want to do is search the People table to find the id's of bob and
fred and add a row to the Couples table containing those id's.... all in one
insert statement.
Is that clearer? Or possible?
Thanks
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
