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]