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
精彩评论