List:General Discussion« Previous MessageNext Message »
From:Steven Buehler Date:August 16 2008 4:04pm
Subject:RE: Multiple Query/Insert help
View as plain text  
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:andy.shellam-lists@stripped] 
Sent: Saturday, August 16, 2008 10:03 AM
To: Martin Gainty
Cc: Steven Buehler; mysql@stripped
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: andy.shellam-lists@stripped
> > To: steve@stripped
> > CC: mysql@stripped
> > 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/mysql?unsub=1
> >
>
> ------------------------------------------------------------------------
> 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>

Thread
Multiple Query/Insert helpSteven Buehler16 Aug
  • Re: Multiple Query/Insert helpAndy Shellam16 Aug
    • RE: Multiple Query/Insert helpMartin Gainty16 Aug
      • Re: Multiple Query/Insert helpAndy Shellam16 Aug
        • RE: Multiple Query/Insert helpSteven Buehler16 Aug
          • Re: Multiple Query/Insert helpAndy Shellam16 Aug
      • RE: Multiple Query/Insert helpSteven Buehler16 Aug
        • RE: Multiple Query/Insert helpMartin Gainty16 Aug
    • RE: Multiple Query/Insert helpSteven Buehler16 Aug