More Fun With SQL

I've mentioned this bit of code recently:

view plainprint?

1. INSERT INTO accession_analysis (  
2.     accession_id ,  analysis_id ,  
3.     reference_id ,  status ,  
4.     status_text  ,  extra_parameters  
5.     )  
6. SELECT  
7.     accession_id ,  
8.     ? , ? , ? , ? , ?, ?  
9. FROM accessions  
10. WHERE request_id = ?  
11. ORDER BY accession_id  

        INSERT INTO accession_analysis (
            accession_id ,  analysis_id ,
            reference_id ,  status ,
            status_text  ,  extra_parameters
            )
        SELECT
            accession_id ,
            ? , ? , ? , ? , ?, ?
        FROM accessions
        WHERE request_id = ?
        ORDER BY accession_id

Well, it's slightly different. Before we were just holding the current state ( waiting, working, success, failure ) and now we're holding text information, too, and you don't just want the current state, you want to be able to look back. So, in addition to accession_analysis , we're adding accession_analysis_status, which will have, so far, a unique id, an id for the AA it connects to, and then the the status information.

And the run now. I have to add a run number to the accession_analysis schema. I can do that, but that's not germane right now.

What is germane is how to store the status into accession_analysis_status at about the same time as it goes into accession_analysis, getting the id from the accession_analysis table. It would be far easier if I was using an iterative approach, but then I'm blasting the DB with many connections instead of just one.

An approach would be to find all the accession_analysis elements without a matching accession_analysis_status, and then inserting them into accession_analysis_status. Something like

view plainprint?

1. INSERT INTO accession_analysis_status (   
2.     aa_id , status , status_text   
3.     )   
4. SELECT id , status ,status_text   
5. FROM accession_analysis   
6. WHERE there's no aa_id corresponding to the id in accession_analysis  

INSERT INTO accession_analysis_status ( 
    aa_id , status , status_text 
    ) 
SELECT id , status ,status_text 
FROM accession_analysis 
WHERE there's no aa_id corresponding to the id in accession_analysis

But clearly, I don't know how to express this as SQL yet.