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: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>
>>
>
>
>
