Re: Confused on Query's

[Brent Baisley]

I assume each part can be associated with multiple projects, which is  
a many to many relation. In that case you need to create a "join"  
table that holds the relation. Table like that are typically just 2  
fields, one for the project id and one for the part id. You may want  
to add other fields like a timestamp so you know when the part was  
added to the project.


Your query on the three tables (projects, projparts, parts) would  
look something like this:

SELECT projects.*, parts.* FROM projects
JOIN projparts ON projects.projectid=projparts.projectid
JOIN parts ON projparts.partsid=parts.partsid
WHERE projects.projectid="1222007BB"

You would change those to left joins if you are not sure whether a  
project has any parts. If there are no parts, that query would not  
return anything.


On Aug 22, 2007, at 5:05 PM, Brian E Boothe wrote:

lets say u have a table called Parts  and another called  
Projects ,,,  how can u associate the Parts Table with the Projects  
table so lets say u wanna add a Specific Part to a project maybe  
even add three parts  from the parts table and associate it with a  
specific project ???


so maybe u would have   projectid = partsid ?

so your project id lets say would be 1222007BB   but u wanna add 7  
parts associated with that Projectid


hmm   maybe whatever partnumber  u add would be associated with the  
projectid  ???


   such as Partnum = projectid ??   with a left join ?

 can someone Give me an example of how i would do this ?   
thanks   alot



i wanna run a query and see the parts  associated with each  
project !  thats my ultimate goal ,,






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





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

Re: Access denied for user on Windows Server

[Alex Kloss]

Car Toper wrote:
> More info...
>
> I am able to login with the account via phpMyAdmin on the server.
> Also, I am trying to use phpMyAdmin to change the permissions, but I
> cannot figure out the syntax:
>
> GRANT ON ppsntracker.* TO [EMAIL PROTECTED] WITH GRANT OPTION;
>
> Cartoper
>
> On 8/21/07, Car Toper <[EMAIL PROTECTED]> wrote:
>   
>> I have a MySql server running on a Windows box that I cannot connect
>> to from another machine.  I first checked to make sure the user has
>> the correct permissions, which the user does, it has the %.  I did
>> read something that gave me the impression the user needs to have a
>> tranditional windows account on the machine, so I created one.  Still
>> nothing.  Is there anything special I need to do to allow access to
>> MySql when the database is running on a different machine?
>>
>> 
>
>   
If you're trying to create a superuser on the server, you would use

GRANT ALL PRIVILEGES ON ppsntracker.* TO [EMAIL PROTECTED] WITH
GRANT OPTION;

Although I wouldn't recommend this for security purposes; it's a much
more secure idea to selectively grant the
privileges as necessary to the user and assign a password, such as

GRANT SELECT,INSERT,DELETE ON ppsntracker.* TO [EMAIL PROTECTED]
IDENTIFIED BY 'password';

And so forth.

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

RE: Why is the average of an int column returned as a string

[Eric Lommatsch]

I have tried that and that seems to be working for me. I am just wondering
why all of the sudden queries that were returning the averages as numeric
values as string values.

Thank you for your suggestion.  


Thank you
 
Eric H. Lommatsch
Programmer
360 Business 
2087 South Grant Street
Denver, CO 80210
Tel 303-777-8939
Fax 303-778-0378
 
[EMAIL PROTECTED]

-Original Message-
From: Rhys Campbell [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 22, 2007 3:34 AM
To: Eric Lommatsch; mysql@lists.mysql.com
Subject: RE: Why is the average of an int column returned as a string

You could use the CAST function, although I have just discovered that MySQL
is rather limited in the types you are able to CAST to...

http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

-Original Message-
From: Eric Lommatsch [mailto:[EMAIL PROTECTED]
Sent: 21 August 2007 22:52
To: mysql@lists.mysql.com
Subject: Why is the average of an int column returned as a string


Hello,
 
I am working with a query that calculates the averages of survey answers. 
 
The survey answers are stored in the database as int(11) fields. When I run
the query the results that I am getting are being returned as string data. 
 
The query that I am working with is a data source for a Crystal Reports
reports. The average columns that are being returned by the query are used in
the report in fields that have been formatted for double values.
 
I am using MySQL 5.0.18 as the database. What would I have to do to get the
averages of Int columns to return as doubles, rather then having to change
all of my columns to be double columns?
 
Thank you
 
Eric H. Lommatsch
Programmer
360 Business
2087 South Grant Street
Denver, CO 80210
Tel 303-777-8939
Fax 303-778-0378
 
[EMAIL PROTECTED]
 

This email is confidential and may also be privileged. If you are not the
intended recipient please notify us immediately by telephoning +44 (0)20 7452
5300 or email [EMAIL PROTECTED] You should not copy it or use it for
any purpose nor disclose its contents to any other person. Touch Local cannot
accept liability for statements made which are clearly the sender's own and
are not made on behalf of the firm.

Touch Local Limited
Registered Number: 2885607
VAT Number: GB896112114
Cardinal Tower, 12 Farringdon Road, London EC1M 3NN
+44 (0)20 7452 5300


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

Confused on Query's

[Brian E Boothe]

lets say u have a table called Parts  and another called Projects ,,,  
how can u associate the Parts Table with the Projects table so lets say 
u wanna add a Specific Part to a project maybe even add three parts  
from the parts table and associate it with a specific project ???


so maybe u would have   projectid = partsid ?

so your project id lets say would be 1222007BB   but u wanna add 7 
parts associated with that Projectid


hmm   maybe whatever partnumber  u add would be associated with the 
projectid  ???


   such as Partnum = projectid ??   with a left join ?

 can someone Give me an example of how i would do this ?  thanks   
alot



i wanna run a query and see the parts  associated with each project !  
thats my ultimate goal ,,






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

Re: MYSQL ENTERPRISE AND COMMUNITY

[Olaf Stein]

AFAIK the only difference is support and the availability of the enterprise
source code (this changes recently)

On 8/22/07 4:10 PM, "krishna chandra prajapati" <[EMAIL PROTECTED]>
wrote:

> Hi Stein,
> 
> I have gone through the link provided below. But still i have the question
> that, whether the mysql database software provided in enterprise edition is
> same as community edition or some other features is there.
> 
> Regards,
> Krishna
> 
> 
> On 8/23/07, Olaf Stein <[EMAIL PROTECTED]> wrote:
>> http://www.mysql.com/products/which-edition.html
>> 
>> 
>> On 8/22/07 3:13 PM, "krishna chandra prajapati" < [EMAIL PROTECTED]
>>  >
>> wrote:
>> 
>>> > hi all,
>>> >
>>> > I am confused about community and enterprise mysql server. Whether
>>> software
>>> > for both is free or enterprise comes with additional features.
>>> > Please give me the details.
>>> >
>>> > regards,
>>> > Krishna
>> 
>> 
>> 
>> 
>> 
>> -
>> Olaf Stein
>> DBA
>> Center for Quantitative and Computational Biology
>> Columbus Children's Research Institute
>> 700 Children's Drive
>> phone: 1-614-355-5685
>> cell: 1-614-843-0432
>> email: [EMAIL PROTECTED]
>> 
> 
> 






-
Olaf Stein
DBA
Center for Quantitative and Computational Biology
Columbus Children's Research Institute
700 Children's Drive
phone: 1-614-355-5685
cell: 1-614-843-0432
email: [EMAIL PROTECTED]

Re: MYSQL ENTERPRISE AND COMMUNITY

[krishna chandra prajapati]

Hi Stein,

I have gone through the link provided below. But still i have the question
that, whether the mysql database software provided in enterprise edition is
same as community edition or some other features is there.

Regards,
Krishna


On 8/23/07, Olaf Stein <[EMAIL PROTECTED]> wrote:
>
> http://www.mysql.com/products/which-edition.html
>
>
> On 8/22/07 3:13 PM, "krishna chandra prajapati" <[EMAIL PROTECTED]>
> wrote:
>
> > hi all,
> >
> > I am confused about community and enterprise mysql server. Whether
> software
> > for both is free or enterprise comes with additional features.
> > Please give me the details.
> >
> > regards,
> > Krishna
>
>
>
>
>
> -
> Olaf Stein
> DBA
> Center for Quantitative and Computational Biology
> Columbus Children's Research Institute
> 700 Children's Drive
> phone: 1-614-355-5685
> cell: 1-614-843-0432
> email: [EMAIL PROTECTED]
>
>

RE: Left join is not doing what I thought it should do.

[Jerry Schwartz]

By using AND, you've moved the test for 79760 into the JOIN condition. I
wouldn't have thought of that, either. This seems to be a peculiarity of a
LEFT JOIN. Normally, if you compare a NULL value against anything, even
another NULL, the result is NULL (neither true nor false). I don't
understand what that would do to an AND operation, I would think the result
would be false.

I hope someone else can explain that to us.

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com


> -Original Message-
> From: Critters [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, August 22, 2007 3:30 PM
> To: Jerry Schwartz
> Cc: 'MySQL General'
> Subject: Re: Left join is not doing what I thought it should do.
>
> It works if I do AND instead of WHERE
> Go figure
>
> LEFT JOIN friends ON gameLeaderboards.userID = friends.userB
> AND friends.userA = 79760
>
> --
> Dave
>
> Jerry Schwartz wrote:
> > I think your problem is that you can't have a "missing"
> friends record that
> > also has a non-null value for friends.userA. If
> friends.userA = 79760, then
> > you've found a record.
> >
> > You can have records where userA is something valid and
> UserB is null, but
> > then you can't join on UserB.
> >
> > Does that help?
> >
> > Regards,
> >
> > Jerry Schwartz
> > The Infoshop by Global Information Incorporated
> > 195 Farmington Ave.
> > Farmington, CT 06032
> >
> > 860.674.8796 / FAX: 860.674.8341
> >
> > www.the-infoshop.com
> > www.giiexpress.com
> > www.etudes-marche.com
> >
> >
> >
> >> -Original Message-
> >> From: Critters [mailto:[EMAIL PROTECTED]
> >> Sent: Wednesday, August 22, 2007 12:23 PM
> >> To: MySQL General
> >> Subject: Left join is not doing what I thought it should do.
> >>
> >> I have 3 tables
> >> A users table (userID, userName)
> >> A leaderboard table (userID, score)
> >> A friends table (userIDA, userIDB)
> >>
> >> I would like to produce the following result:
> >>
> >> userName, score, userIDA
> >> Dave, 100, 1
> >> Simon, 200, 5
> >> Paul, 300, NULL
> >>
> >> The 3rd record is NULL as there is no record in "friends"
> >> with a userIDB
> >> matching users (or leaderboard) userID
> >>
> >> I have tried this:
> >>
> >> SELECT users.username, gameLeaderboards.playerpoints, friends.userA
> >> FROM gameLeaderboards
> >> JOIN users ON gameLeaderboards.userID = users.ID
> >> LEFT JOIN friends ON gameLeaderboards.userID = friends.userB
> >> WHERE friends.userA = 79760
> >>
> >> The where is so there is only a value in the "userIDA"
> column if the
> >> user is friends with userID 79760
> >>
> >> But what I get instead of lots of records with 79760 and
> >> NULLs is just
> >> records from the leaderboard table that have a matching
> userID in the
> >> friend table, hmmf
> >>
> >> Any ideas?
> >>
> >> Thanks
> >> -
> >> Dave
> >>
> >>
> >> --
> >> 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: MYSQL ENTERPRISE AND COMMUNITY

[Olaf Stein]

http://www.mysql.com/products/which-edition.html


On 8/22/07 3:13 PM, "krishna chandra prajapati" <[EMAIL PROTECTED]>
wrote:

> hi all,
> 
> I am confused about community and enterprise mysql server. Whether software
> for both is free or enterprise comes with additional features.
> Please give me the details.
> 
> regards,
> Krishna





-
Olaf Stein
DBA
Center for Quantitative and Computational Biology
Columbus Children's Research Institute
700 Children's Drive
phone: 1-614-355-5685
cell: 1-614-843-0432
email: [EMAIL PROTECTED]


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

Re: Left join is not doing what I thought it should do.

[Critters]

It works if I do AND instead of WHERE
Go figure

LEFT JOIN friends ON gameLeaderboards.userID = friends.userB
AND friends.userA = 79760

--
Dave

Jerry Schwartz wrote:

I think your problem is that you can't have a "missing" friends record that
also has a non-null value for friends.userA. If friends.userA = 79760, then
you've found a record.

You can have records where userA is something valid and UserB is null, but
then you can't join on UserB.

Does that help?

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com


  

-Original Message-
From: Critters [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 22, 2007 12:23 PM
To: MySQL General
Subject: Left join is not doing what I thought it should do.

I have 3 tables
A users table (userID, userName)
A leaderboard table (userID, score)
A friends table (userIDA, userIDB)

I would like to produce the following result:

userName, score, userIDA
Dave, 100, 1
Simon, 200, 5
Paul, 300, NULL

The 3rd record is NULL as there is no record in "friends"
with a userIDB
matching users (or leaderboard) userID

I have tried this:

SELECT users.username, gameLeaderboards.playerpoints, friends.userA
FROM gameLeaderboards
JOIN users ON gameLeaderboards.userID = users.ID
LEFT JOIN friends ON gameLeaderboards.userID = friends.userB
WHERE friends.userA = 79760

The where is so there is only a value in the "userIDA" column if the
user is friends with userID 79760

But what I get instead of lots of records with 79760 and
NULLs is just
records from the leaderboard table that have a matching userID in the
friend table, hmmf

Any ideas?

Thanks
-
Dave


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

MYSQL ENTERPRISE AND COMMUNITY

[krishna chandra prajapati]

hi all,

I am confused about community and enterprise mysql server. Whether software
for both is free or enterprise comes with additional features.
Please give me the details.

regards,
Krishna

RE: Left join is not doing what I thought it should do.

[Jerry Schwartz]

I think your problem is that you can't have a "missing" friends record that
also has a non-null value for friends.userA. If friends.userA = 79760, then
you've found a record.

You can have records where userA is something valid and UserB is null, but
then you can't join on UserB.

Does that help?

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com


> -Original Message-
> From: Critters [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, August 22, 2007 12:23 PM
> To: MySQL General
> Subject: Left join is not doing what I thought it should do.
>
> I have 3 tables
> A users table (userID, userName)
> A leaderboard table (userID, score)
> A friends table (userIDA, userIDB)
>
> I would like to produce the following result:
>
> userName, score, userIDA
> Dave, 100, 1
> Simon, 200, 5
> Paul, 300, NULL
>
> The 3rd record is NULL as there is no record in "friends"
> with a userIDB
> matching users (or leaderboard) userID
>
> I have tried this:
>
> SELECT users.username, gameLeaderboards.playerpoints, friends.userA
> FROM gameLeaderboards
> JOIN users ON gameLeaderboards.userID = users.ID
> LEFT JOIN friends ON gameLeaderboards.userID = friends.userB
> WHERE friends.userA = 79760
>
> The where is so there is only a value in the "userIDA" column if the
> user is friends with userID 79760
>
> But what I get instead of lots of records with 79760 and
> NULLs is just
> records from the leaderboard table that have a matching userID in the
> friend table, hmmf
>
> Any ideas?
>
> Thanks
> -
> Dave
>
>
> --
> 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]

Left join is not doing what I thought it should do.

[Critters]

I have 3 tables
A users table (userID, userName)
A leaderboard table (userID, score)
A friends table (userIDA, userIDB)

I would like to produce the following result:

userName, score, userIDA
Dave, 100, 1
Simon, 200, 5
Paul, 300, NULL

The 3rd record is NULL as there is no record in "friends" with a userIDB 
matching users (or leaderboard) userID


I have tried this:

SELECT users.username, gameLeaderboards.playerpoints, friends.userA
FROM gameLeaderboards
JOIN users ON gameLeaderboards.userID = users.ID
LEFT JOIN friends ON gameLeaderboards.userID = friends.userB
WHERE friends.userA = 79760

The where is so there is only a value in the "userIDA" column if the 
user is friends with userID 79760


But what I get instead of lots of records with 79760 and NULLs is just 
records from the leaderboard table that have a matching userID in the 
friend table, hmmf


Any ideas?

Thanks
-
Dave


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

Re: How to limit word result by query

[mos]

At 12:40 AM 8/22/2007, [EMAIL PROTECTED] wrote:

Hi all,
Is it possible to do query which limiting the result into some words
only? i.e the complete sentence is "I am able to login with the account"
and I just want to view "I am able to login..." Many thanks for any
reply.

Regards,


Willy


Willy,
  If you want to filter the rows to just the words "I am able to 
login"  and it doesn't have to be in that exact order then use full text 
search. http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html


If you want to look for the exact phrase, then you can use fulltext 
search to narrow down the range to a few rows that has those words then add 
"and mycol like '%I am able to login%' to the Where clause of the Select 
statement. This will make the Like operator much faster.


Mike 


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

Re: MySql priority

[Rolando Edwards \(DBA\)]

You may want to consider tuning the 'his-priority' server instance by giving it 
larger 
  values for the following:
   
  temp_table_size
  query_cache_size
query_cache_limit

Ben Clewett <[EMAIL PROTECTED]> wrote:
  Sorry if this has been asked many times before...

I have 5 MySql systems running on a single server.

I want one to run with maximum priority, the other four to run with minimum.

Can this me archived using something as simple as 'nice':

# nice -10 mysqld --defaults-file=his_priorty
# nice 10 mysqld --defaults-file=low_priorty_1
# nice 10 mysqld --defaults-file=low_priorty_2
# nice 10 mysqld --defaults-file=low_priorty_3
# nice 10 mysqld --defaults-file=low_priorty_4

Or is there a better way that people use?

Thanks in advance...

Ben

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



   
-
Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, 
photos & more. 

MySql priority

[Ben Clewett]

Sorry if this has been asked many times before...

I have 5 MySql systems running on a single server.

I want one to run with maximum priority, the other four to run with minimum.

Can this me archived using something as simple as 'nice':

# nice -10 mysqld --defaults-file=his_priorty
# nice 10 mysqld --defaults-file=low_priorty_1
# nice 10 mysqld --defaults-file=low_priorty_2
# nice 10 mysqld --defaults-file=low_priorty_3
# nice 10 mysqld --defaults-file=low_priorty_4

Or is there a better way that people use?

Thanks in advance...

Ben

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

RE: Why is the average of an int column returned as a string

[Rhys Campbell]

You could use the CAST function, although I have just discovered that MySQL
is rather limited in the types you are able to CAST to...

http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

-Original Message-
From: Eric Lommatsch [mailto:[EMAIL PROTECTED]
Sent: 21 August 2007 22:52
To: mysql@lists.mysql.com
Subject: Why is the average of an int column returned as a string


Hello,
 
I am working with a query that calculates the averages of survey answers. 
 
The survey answers are stored in the database as int(11) fields. When I run
the query the results that I am getting are being returned as string data. 
 
The query that I am working with is a data source for a Crystal Reports
reports. The average columns that are being returned by the query are used
in
the report in fields that have been formatted for double values.
 
I am using MySQL 5.0.18 as the database. What would I have to do to get the
averages of Int columns to return as doubles, rather then having to change
all of my columns to be double columns?
 
Thank you
 
Eric H. Lommatsch
Programmer
360 Business 
2087 South Grant Street
Denver, CO 80210
Tel 303-777-8939
Fax 303-778-0378
 
[EMAIL PROTECTED]
 

This email is confidential and may also be privileged. If you are not the 
intended recipient please notify us immediately by telephoning +44 (0)20 7452 
5300 or email [EMAIL PROTECTED] You should not copy it or use it for any 
purpose nor disclose its contents to any other person. Touch Local cannot 
accept liability for statements made which are clearly the sender's own and are 
not made on behalf of the firm.

Touch Local Limited
Registered Number: 2885607
VAT Number: GB896112114
Cardinal Tower, 12 Farringdon Road, London EC1M 3NN
+44 (0)20 7452 5300


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