List:General Discussion« Previous MessageNext Message »
From:Andy Shellam Date:August 16 2008 5:45pm
Subject:Re: Multiple Query/Insert help
View as plain text  
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>
>>     
>
>
>   
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