insert with multiple subqueries

Hi

what i like to do is insert in one table 2 value from 2 different row.

exp:

table1: person

id name

1 bob

2 john

so id like to make an insert that will result in this

table 2: person_knowed

idperson1: 1

idperson2: 2

so the wuery should look something like this:

insert into person_knowed(idperson1, idperson2)
(select id from personwhere name = 'bob',
select id from personwhere name = 'john'));

anybody have an idea of how to acheive this?

insert into person_knowed(idperson1, idperson2)
select A.id, B.id from
(select id from personwhere name = 'bob')A,
(select id from personwhere name = 'john')B

This will work as you are expecting it to work only if the subqueries return 1 row each. If they return more than 1 row, you will end up with a cross-join

Insert Subqueries

I have tried this insert comand and it errors out telling me that i
cannot use subqueries this way. INSERT INTO tblPartLocation
(PartLocation, Part)VALUES (999,(SELECT PartID FROM tblParts WHERE
PartName = 'test'))

how would i insert a value from a query?

thanks for any helpThe syntax you are looking for is:

INSERT INTO tblPartLocation (PartLocation, Part)
SELECT 999, PartID
FROM tblParts
WHERE PartName = 'test'

However, you may also want to reexamine your schema. Don't use tbl as
a prefix for your tables; it's redundant, and unnecessary. Also, you
have a column named Part in one table, but you're inserting the values
of PartID from another table. If the columns represent the same thing,
why don't you name them the same?

HTH,
Stu

pltaylor3@.gmail.com wrote:

Quote:

Originally Posted by

I have tried this insert comand and it errors out telling me that i
cannot use subqueries this way. INSERT INTO tblPartLocation
(PartLocation, Part)VALUES (999,(SELECT PartID FROM tblParts WHERE
PartName = 'test'))
>
how would i insert a value from a query?
>
thanks for any help

|||thanks for your reply...the names are legacy. Just trying to make it
more functional.
Stu wrote:

Quote:

Originally Posted by

The syntax you are looking for is:
>
INSERT INTO tblPartLocation (PartLocation, Part)
SELECT 999, PartID
FROM tblParts
WHERE PartName = 'test'
>
However, you may also want to reexamine your schema. Don't use tbl as
a prefix for your tables; it's redundant, and unnecessary. Also, you
have a column named Part in one table, but you're inserting the values
of PartID from another table. If the columns represent the same thing,
why don't you name them the same?
>
HTH,
Stu
>
>
pltaylor3@.gmail.com wrote:

Quote:

Originally Posted by

I have tried this insert comand and it errors out telling me that i
cannot use subqueries this way. INSERT INTO tblPartLocation
(PartLocation, Part)VALUES (999,(SELECT PartID FROM tblParts WHERE
PartName = 'test'))

how would i insert a value from a query?

thanks for any help