MySQL Error Number 1045 Access denied

[AndrewMcHorney]

Hello

I am still getting the MySQL Error Number 1045 Access denied error 
message when running the adminstrator gui. I attempted to start mysql 
from the dos command line via "mysql" and I am getting the following. 
ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using 
password: NO). I am logging in as root, locahost and no password 
because I did not enter one when I installed the software. I will be 
changing that. For the command line I just entered mysql and a 
return. How can I fix this. I am off for the week and I would like to 
get this working so I can create a database or 2.


Andrew


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Multiple Query/Insert help

[Andy Shellam]

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.

Typical Maintenance for InnoDB Tables

[samk]

See Thread at: http://www.techienuggets.com/Detail?tx=48414 Posted on behalf of 
a User

I have a MySQL 5.0 InnoDB database that's about 1 GB in size so it's still 
pretty tiny. Is there any performance enhancement maintenance that should be 
done on the tables? I do a weekly Optimize through the MySQL Admin tool, which 
doesn't appear to do anything, I presume it updates the table statistics. Is 
there anything else that I should do with the indexes for instance? 

Thanks for any information.

John T.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Multiple Query/Insert help

[Steven Buehler]

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


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Multiple Query/Insert help

[Martin Gainty]

Hi Steve-
apparently the column is not defined to that entity
try replacing s1.div_id reference with ts.div_id or even better 
team_season.div_id
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. 


> From: [EMAIL PROTECTED]
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> CC: mysql@lists.mysql.com
> Subject: RE: Multiple Query/Insert help
> Date: Sat, 16 Aug 2008 10:10:15 -0500
> 
> Thank you, but I still get an error and I can't figure it out: " Unknown
> column 's1.div_id' in 'on clause'"
> Any other thoughts?
> 
> Steve
> 
> -Original Message-
> From: Martin Gainty [mailto:[EMAIL PROTECTED] 
> Sent: Saturday, August 16, 2008 9:50 AM
> To: Andy Shellam; Steven Buehler
> Cc: mysql@lists.mysql.com
> Subject: RE: Multiple Query/Insert help
> 
> 
> 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_id
>   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.
> 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]
> 

_
See what people are saying about Windows Live.  Check out featured posts.
http://www.windowslive.com/connect?ocid=TXT_TAGLM_WL_connect2_082008

RE: Multiple Query/Insert help

[Steven Buehler]

Thank you, but I still get an error and I can't figure it out: " Unknown
column 's1.div_id' in 'on clause'"
Any other thoughts?

Steve

-Original Message-
From: Martin Gainty [mailto:[EMAIL PROTECTED] 
Sent: Saturday, August 16, 2008 9:50 AM
To: Andy Shellam; Steven Buehler
Cc: mysql@lists.mysql.com
Subject: RE: Multiple Query/Insert help


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_id
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.
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]

RE: Multiple Query/Insert help

[Steven Buehler]

Thank you, but I still get an error and I can't figure it out: " Unknown
column 's1.div_id' in 'on clause'"
Any other thoughts?

Steve

-Original Message-
From: Andy Shellam [mailto:[EMAIL PROTECTED] 
Sent: Saturday, August 16, 2008 9:40 AM
To: Steven Buehler
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]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Multiple Query/Insert help

[Andy Shellam]

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! 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Multiple Query/Insert help

[Martin Gainty]

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.
http://www.windowslive.com/connect?ocid=TXT_TAGLM_WL_connect2_082008

Re: Multiple Query/Insert help

[Andy Shellam]

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]

RE: another INNODB vs MYISAM question

[Martin Gainty]

Good Morning Mike and Brent

Ive been following and implementing MYSQL tuning suggestions at
http://dev.mysql.com/doc/refman/5.1/en/innodb-tuning.html

I did'nt see any suggestions on converting the entire DB to INNODB 
or converting the individual tables to INNODB

Suggestions?
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. 


> From: [EMAIL PROTECTED]
> To: [EMAIL PROTECTED]
> Subject: Re: another INNODB vs MYISAM question
> Date: Sat, 16 Aug 2008 08:52:51 -0400
> CC: mysql@lists.mysql.com
> 
> First, databases do not have a table type, they are mainly just a  
> logical grouping of tables. Mixing table types in a database is quite  
> alright and is what you are supposed to do. I generally use MYISAM,  
> but if I have a table with lots of activity (inserts, deletes,  
> selects) or needs transaction support, I use InnoDB.
> 
> What you did was not only switch the default table type, but you  
> disabled the InnoDB table type. As you may already know, MySQL's table  
> types are different engines that are really plug-ins. You can disable  
> those plug-ins if you like, which is what you did. Just re-enable the  
> InnoDB stuff and you should be alright. You can leave your default  
> engine as MYISAM and if you like, you can use ALTER TABLE to convert  
> your InnoDB tables to MYISAM.
> 
> --
> Brent Baisley
> 
> 
> On Aug 15, 2008, at 1:01 AM, [EMAIL PROTECTED] wrote:
> 
> > Hello mysql,
> >
> > As I have previously mentioned, I installed WAMPSERVER 2.0 on my
> > Windows XP pro box recently. It installed INNODB as the Default
> > Engine.
> >
> > All of my legacy Databases are MYISAM and after the installation, I
> > copied them all into the DATA folder and everything worked, even
> > adding new tables etc. but the new stuff was INNODB. So I ended up
> > with some MYISAM databases that contained INNODB tables in them.
> >
> > After a few weeks I got to thinking that mixing INNODB and MYISAM
> > might not be a good thing and switched the Default Engine to MYISAM in
> > my.ini file. I didn't just switch the default, I commented out all the
> > INNODB calls in the my.ini file as well.
> >
> > As I half expected, all the databases that I had added INNODB tables
> > failed when I tried to fire up the applications that used them.
> >
> > Although I am not new to mysql, I have had a bit of MYISAM "tunnel
> > vision" with it so my question is, if I had just switched the default
> > engine and NOT disabled the INNODB calls in my.ini, would that have
> > prevented the "problem"? I restored all the MYISAM files and got
> > everything back working again.
> >
> > I don't want to go through the "lengthy" reproduction exercise of
> > reinstalling everything to test the theory so if someone has had some
> > experience with this, I would appreciate hearing from them.
> >
> > -- 
> > Best regards,
> > mikesz  mailto:[EMAIL PROTECTED]
> >
> >
> > -- 
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> >
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

_
Talk to your Yahoo! Friends via Windows Live Messenger.  Find out how.
http://www.windowslive.com/explore/messenger?ocid=TXT_TAGLM_WL_messenger_yahoo_082008

Multiple Query/Insert help

[Steven Buehler]

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

Re: another INNODB vs MYISAM question

[Brent Baisley]

First, databases do not have a table type, they are mainly just a  
logical grouping of tables. Mixing table types in a database is quite  
alright and is what you are supposed to do. I generally use MYISAM,  
but if I have a table with lots of activity (inserts, deletes,  
selects) or needs transaction support, I use InnoDB.


What you did was not only switch the default table type, but you  
disabled the InnoDB table type. As you may already know, MySQL's table  
types are different engines that are really plug-ins. You can disable  
those plug-ins if you like, which is what you did. Just re-enable the  
InnoDB stuff and you should be alright. You can leave your default  
engine as MYISAM and if you like, you can use ALTER TABLE to convert  
your InnoDB tables to MYISAM.


--
Brent Baisley


On Aug 15, 2008, at 1:01 AM, [EMAIL PROTECTED] wrote:


Hello mysql,

As I have previously mentioned, I installed WAMPSERVER 2.0 on my
Windows XP pro box recently. It installed INNODB as the Default
Engine.

All of my legacy Databases are MYISAM and after the installation, I
copied them all into the DATA folder and everything worked, even
adding new tables etc. but the new stuff was INNODB. So I ended up
with some MYISAM databases that contained INNODB tables in them.

After a few weeks I got to thinking that mixing INNODB and MYISAM
might not be a good thing and switched the Default Engine to MYISAM in
my.ini file. I didn't just switch the default, I commented out all the
INNODB calls in the my.ini file as well.

As I half expected, all the databases that I had added INNODB tables
failed when I tried to fire up the applications that used them.

Although I am not new to mysql, I have had a bit of MYISAM "tunnel
vision" with it so my question is, if I had just switched the default
engine and NOT disabled the INNODB calls in my.ini, would that have
prevented the "problem"? I restored all the MYISAM files and got
everything back working again.

I don't want to go through the "lengthy" reproduction exercise of
reinstalling everything to test the theory so if someone has had some
experience with this, I would appreciate hearing from them.

--
Best regards,
mikesz  mailto:[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]