Thank you Martin and Andy for your help in this matter. I redid the queries taking hints from yours and came up with one that worked. I am not that great with INNER JOIN's and couldn't get yours to work, even though you did still say that I needed to tweek it. It would be nice to know the query that would work with an INNER JOIN though. Just so that I can study it and learn from it.
INSERT INTO games2( sea_id, date, time, loc_id, hteam, vteam, div_id ) ( SELECT '36' AS sea_id, s1.game_date AS date, s1.begin_time AS time, s1.loc_id AS loc_id, s1.home_team_id AS hteam, s1.away_team_id AS vteam, ( SELECT t1.div_id AS div_id FROM team_season t1 WHERE t1.team_id = s1.home_team_id AND t1.deleted != '1' ) AS div_id FROM scheduler s1 ) Thanks To All Steve -----Original Message----- From: Andy Shellam [mailto:[EMAIL PROTECTED] Sent: Saturday, August 16, 2008 10:03 AM To: Martin Gainty Cc: Steven Buehler; mysql@lists.mysql.com Subject: Re: Multiple Query/Insert help Hi Martin, Good point, I normally do but was just illustrating the join. I would also normally fully-qualify each column when using table aliases and multiple tables to avoid disambiguity. insert into games2 (sea_id,date,time,loc_id,hteam,vteam,div_id) ( select '36' as sea_id, s1.game_date as date, s1.begin_time as time, s1.loc_id as loc_id, s1.home_team_id as hteam, s1.away_team_id as vteam, ts.div_id as div_ud from scheduler s1 INNER JOIN team_season ts ON ts.team_id = s1.div_id ) Thanks, Andy Martin Gainty wrote: > the only possible suggestion i have would be to disambiguate the > selected columns with 'as' > > insert into games2 (sea_id,date,time,loc_id,hteam,vteam,div_id) > ( > select > '36' as sea_id, game_date as date, begin_time as time, loc_id as > loc_id, home_team_id as hteam, away_team_id as vteam, ts.div_id as div_ud > > from > > scheduler s1 INNER JOIN team_season ts ON ts.team_id = s1.div_id > > ) > > Thanks Andy > Martin > ______________________________________________ > Disclaimer and confidentiality note > Everything in this e-mail and any attachments relates to the official > business of Sender. This transmission is of a confidential nature and > Sender does not endorse distribution to any party other than intended > recipient. Sender does not necessarily endorse content contained > within this transmission. > > > > Date: Sat, 16 Aug 2008 15:40:08 +0100 > > From: [EMAIL PROTECTED] > > To: [EMAIL PROTECTED] > > CC: mysql@lists.mysql.com > > Subject: Re: Multiple Query/Insert help > > > > Hi Steve, > > > > You're seeing this error because this query: > > > > select div_id > > from team_season where team_id=s1.div_id > > > > is being run independently of the rest, so it doesn't know of "s1" in > > this context. You would probably be better with an INNER JOIN here, > > something like the following (may need tweaking): > > > > e.g. > > > > insert into games2 (sea_id,date,time,loc_id,hteam,vteam,div_id) > > ( > > select > > '36', game_date, begin_time, loc_id, home_team_id, away_team_id, > ts.div_id > > from > > scheduler s1 INNER JOIN team_season ts ON ts.team_id = s1.div_id > > ) > > > > > > Regards, > > Andy > > > > Steven Buehler wrote: > > > I have a query that I just can't seem to get working. > > > > > > > > > > > > insert into games2 (sea_id,date,time,loc_id,hteam,vteam,div_id) > (select > > > '36',game_date,begin_time,loc_id,home_team_id,away_team_id,(select > div_id > > > from team_season where team_id=s1.div_id) from scheduler s1); > > > > > > > > > > > > Of course, I am getting the dreaded "Unknown column 's1.div_id' in > 'where > > > clause'" error message. > > > > > > I think the statement above shows what I am trying to do better > than me > > > explaining it. My only real problem is that the div_id needs to be > gotten > > > from the team_season table. > > > > > > > > > > > > Any help would be appreciated > > > > > > Thanks > > > > > > Steve > > > > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > ------------------------------------------------------------------------ > See what people are saying about Windows Live. Check out featured > posts. Check It Out! > <http://www.windowslive.com/connect?ocid=TXT_TAGLM_WL_connect2_082008> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]