Inserting multiple rows with similar data

[bman]

Hello,

 

In my db I have three tables. One holds data for all the people currently in the system (call it "persons"), another holds all possible interests ("interests"), and the third lists the actual interests people in the system have ("person_interests"). Each row in person_interests contains person_id and interest_id.

 

So here's my issue. One of the possible interests is "All." Rather than give this its own entry, I want to grab everything from interests and insert it into person_interests along with the person_id of whoever selects this choice. For example:

 

persons:

---------

id  | name

---------

42  Bob

---------

 

interests:

---------

id | name

---------

1    Hiking

2    Boating

3    Biking

---------

 

If Bob selects "All", then person_interests will have

 

----------------------

person_id | interest_id

----------------------

42            1

42            2

42            3

 

Is there a way to do this with one query? Thanks.

[xtopolis]

Hi,

After some googling I found this to work, however, I did not thoroughly test it.  (Source: Comment #2)

 

INSERT INTO person_interests( person_id, interest_id )
SELECT p.person_id, i.interest_id
FROM persons p, interests i
WHERE p.person_id = 42