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:peter.brawley@stripped]
Sent: Wed 6/28/2006 4:01 PM
To: Orton, Steve
Cc: mysql@stripped
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 )
context_id=@contextID 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:
> Hello fellow listers,
>
> I'm currently trying to reduce our TCO by incorporating this fine DBMS and
> replacing the MS SQLServer we're using. I'm trying to re-implement the stored procedures
> written for SQLServer to MySQL and have one that's tough to figure out.
>
> This stored procedure basically finds ID values in one table that fit criteria
> based on set flags in the table and puts them in another table along with a context value
> that is assigned to the 'flag' settings. This 'master_context_list' table holds all the
> IDs assigned to each context value. So when the flags are changed or another context is
> created, the table is updated with new ID values that fit the new criteria.
>
> The stored procedures make extensive use of MS shortcuts to create tables, assign
> values, etc. This is the MS SQL statement:
>
> 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 )
>
> So this is taking all the Target IDs that match in both 'targets_list' and the
> temporary table APPLICABLE_TARGET_IDS and inserting them into 'master_context_list' along
> with the Context_ID with the value of @ContextID. This sort of shorthand is nice for MS
> users, bad for the rest of us.
>
> When I try to run this in a MySQL stored procedure, I get this error:
> "Unknown column 'db_context_ID' in field list"
>
> I know why it's failing but there's a failure in translation. Can anybody figure
> out how to translate this into SQL that will work in MySQL?
>
> Thanks in advance for any help....steve---
>
> Steven J Orton
> Software Engineer
> Northrop Grumman Mission Systems
> Middletown, RI 02842
>
>
>
--
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
