> What if @ContextID is an input variable to the procedure. What can we do then? >I'm sorry if this seems very academic, but I'm rather new to the stored procedure >thing and only a little better at SQL statements as a whole:)Give the param a name which cannot conflict with an existing column name, eg pcontextid, then write INSERT INTO master_context_list (Context_id, Target_ID) SELECT pcontextid, Target_ID FROM applicable_target_ids; >I'm sorry if this seems very academic, but I'm rather new to the stored procedure >thing and only a little better at SQL statements as a whole:)Nothing academic about, and being a recovering M$SQL user ain't easy :-) . PB ----- Orton, Steve wrote: What if @ContextID is an input variable to the procedure. What can we do then? I'm sorry if this seems very academic, but I'm rather new to the stored procedure thing and only a little better at SQL statements as a whole:)Steven J Orton Software Engineer Northrop Grumman Mission Systems Middletown, RI 02842 -----Original Message----- From: Peter Brawley [mailto:[EMAIL PROTECTED]] Sent: Wed 6/28/2006 4:57 PM To: Orton, Steve; [email protected] Subject: Re: Tough query to crack />I don't think that will work because Context_ID is not found in 'targets_list' hence the error. >That's why I thought the Context_ID field was being assigned with the value of @ContextID. >That's the only way we can get this value. /Perhaps not, but no matter./ / />What I would like is Context_ID to be filled with the user var. @Context_ID and the Target_IDs >found in both the tables. So if the input is 3, then each entry should be 3 and any target ID >found with the flags associated with context 3 applied. These flags are processed earlier in >this same procedure and the IDs are put into the APPLICABLE_TARGET_IDS temporary table. / 1. To reference a user variable @contextID (NOT a declared sproc variable) you need only: INSERT ... SELECT @contextID, ... 2. If all required flagged targetIDs are in applicable_target_ids, you do not need the join either: INSERT INTO master_context_list (Context_ID, Target_ID) SELECT @contextID, Target_ID FROM applicable_target_ids; PB ----- Orton, Steve wrote:I don't think that will work because Context_ID is not found in 'targets_list' hence the error. That's why I thought the Context_ID field was being assigned with the value of @ContextID. That's the only way we can get this value. What I would like is Context_ID to be filled with the user var. @Context_ID and the Target_IDs found in both the tables. So if the input is 3, then each entry should be 3 and any target ID found with the flags associated with context 3 applied. These flags are processed earlier in this same procedure and the IDs are put into the APPLICABLE_TARGET_IDS temporary table. steve--- Steven J Orton Software Engineer Northrop Grumman Mission Systems Middletown, RI 02842 -----Original Message----- From: Peter Brawley [mailto:[EMAIL PROTECTED]] Sent: Wed 6/28/2006 4:01 PM To: Orton, Steve Cc: [email protected] Subject: Re: Tough query to crack Steve, > INSERT INTO master_context_list (Context_ID, Target_ID) > SELECT Context_ID = @ContextID, targets_list.Target_ID FROM targets_list > WHERE Target_ID IN ( SELECT Target_ID FROM #APPLICABLE_TARGET_IDS ) [EMAIL PROTECTED] tells the server to return 1 when the column value of context_id is equal to the value of the user variable @contextID, and otherwise zero. Not likely what you intend. Apart from that, does the following, in standard SQL, represent what you are trying to accomplish? INSERT INTO master_context_list (Context_ID, Target_ID) SELECT l.Context_ID, l.Target_ID FROM targets_list AS l INNER JOIN applicable_target_ids AS a USING (target_id); PB ----- Orton, Steve wrote: |
No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.5/377 - Release Date: 6/27/2006
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
