reverse engneering a query

Question

I have the following query:

SELECT a.catalogID, d.catalogFileID, e.catalogFileID 
FROM catalog_topics a
LEFT JOIN catalog_files_join b ON a.catalogID = b.foreignKey
LEFT JOIN catalog_files_join c ON c.foreignKey = b.catalogFileID
LEFT JOIN catalog_files d ON d.catalogFileID = c.catalogFileID
LEFT JOIN catalog_files e ON e.catalogFileID = b.catalogFileID
WHERE b.fileTypeID =  'gvl401'
AND c.fileTypeID =  'gvl25'
AND a.topicID =  'top312'
ORDER BY a.sortOrder;

which returns following results:

88  cfil923     cfil922
89  cfil925     cfil924
91  cfil929     cfil928
171   cfil107     cfil1074

I want to write an insert statement to insert few records such a way that if I insert 2 records the above select query returns 6 records. While inserting I will have access to everything that is there in above where clause and I will be inserting new d.catalogFileID and e.catalogFileID

Answer 1

The query that you supplied pulls data from 3 separate tables. A single SQL insert query will only insert data into a single table. Thus in order to add each record to the system you are going to have to do at least 3 inserts per record. ie

For a new record of 10, cfil123, cfil456 you would need:

INSERT INTO catalog_topics(catalogID, .. other required fields) VALUE(10, other required fields)
INSERT INTO catalog_files(catalogFileID, .. other required fields) VALUE('cfil123', other  required fields) 
INSERT INTO catalog_files_join(catalogFileID, .. other required fields) VALUE('cfil456', other  required fields)

In addition you should wrap the set of inserts with a start/end transaction pair so that either all of the inserts per record happen or none of them happen.