MySQL Multiple Insert and Join

I have a table called media_files with columns:

id
listing_id
file_id

I have another accounts table with columns:

id
group_id

My media_files table currently has 1 listing per group_id (taken from the associated accounts table). Accounts table has all accounts grouped into group_id.

I want to create a listing in the media_files table for each row in the accounts table. The listing_id must be taken from the accounts table, and the file_id must be the same for all rows with matching group_id.

I 开发者_Go百科know I have to use a temp table and/or a join, but I am not sure how to accomplish this.

Any help is very much appreciated. Thanks for reading.

One insert, two selects and two joins later, and I got it.

INSERT INTO media_files (app_id, listing_id, fid) 
    SELECT mymaster.app_id, mymaster.rlisting_id, mymaster.fid
    from (
     SELECT ch.id AS rlisting_id, cb.* from accounts ch INNER JOIN (
       SELECT ca.group_id, mf.* FROM accounts ca INNER JOIN media_files mf ON mf.listing_id = ca.id WHERE mf.app_id=1085
        ) cb ON ch.group_id = cb.group_id
    ) mymaster