Hi Steve,
As Martin pointed out, the column "div_id" doesn't appear to exist in
your scheduler table. I notice that in your query that now works,
you're linking the field "home_team_id" to team_id in the team_season
table, not "div_id" as specified in your original query. Perhaps this
might be why your original query didn't work in the first place?
Does this not work?
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.home_team_id AND ts.deleted != '1')
)
or this might be a bit more "correct":
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.home_team_id
where
ts.deleted != '1'
)
Andy
Steven Buehler wrote:
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]
