If I were keeping tract of high school sports statistics and thus designed
the following tables:
sports, rules, statistical definitions
and
players, teams, games
Would it be a good or bad idea to put the first set of tables in a separate
database called "library" since they are used for
If I insert a record into a table with an auto increment ID how can I get
that records ID value? I have read about SELECT LAST_INSERT_ID() statement,
however, do not two statements introduce the risk that another insert may
occur in the interum? Is there a full proof way of getting the ID of the
If I have three simple tables:
mysql> select * from customer;
+++
| ID | NAME |
+++
| 1 | Joey |
| 2 | Mike |
| 3 | Kellie |
+++
3 rows in set (0.00 sec)
mysql> select * from fruit;
++-+
| ID | NAME|
++-+
| 1 | Apples |
|
that have
are linked to p.PTRN_ID=1. So p.PTRN_ID!=1. Thanks nonetheless for trying.
This may be unsolvable.
2015-09-04 12:30 GMT-05:00 Peter Brawley <peter.braw...@earthlink.net>:
> On 2015-09-04 11:39 AM, Richard Reina wrote:
>
>
> 2015-09-04 11:18 GMT-05:00 Peter B
I have a column name quarter which I need to have 5 possible inputs; 1, 2,
3, 4, or OT. Because of the OT possibility I am leaning towards ENUM.
Hence, I am also thus considering ENUM('first', 'second', 'third',
'fourth', 'overtime') as the input will primarily be used in written
descriptions. Is
I have the following two tables;
mysql> select * from challenge;
+++-++--+-+--+
| ID | PLR_ID | ACC_TYPE_ID | season | year | CHAR_ID |
+++-++--+-+--+
| 1 | 1 |
2015-09-04 11:18 GMT-05:00 Peter Brawley <peter.braw...@earthlink.net>:
> On 2015-09-04 9:40 AM, Richard Reina wrote:
>
>> I have the following two tables;
>>
>> mysql> select * from challenge;
>> +++-++--+---
I am writing a web application in perl that will create, edit, update and
delete data from a MySQL database. I have written a perl module that will
manage the connections (issue database handles ). As new users sign up for
the application should each get their own MySQL username and password or is
I am ceating a database application with two different types of users,
clients and technicians. Both types of users have to create and account in
which they become users. From there they can become clients or
technicians or perhaps even both. Since each type describe different
attributes -- user
Message
From: Richard Reina gatorre...@gmail.com
Reply-To: Richard Reina gatorre...@gmail.com
Date: 07/29/15 10:19 AM
To: mysql@lists.mysql.com mysql@lists.mysql.com
Cc:
Sub: table design question
If I were to create a database table(s) to tract most common repairs to
different appliances I
If I were to create a database table(s) to tract most common repairs to
different appliances I can't decide if it would be better to create one
table with a long ENUM column that contains repairs that could be
attributed to any appliance or different repair tables for each appliance.
All the
I have used MySQL for about twelve years as a database on our private LAN
that has only a handful of users at a time that query about a dozen
databases. The current server is an old rack-mounted machine that is
somewhat of an energy hog and is due to be replaced. I was considering
replacing it
, a las 2:00 AM, Manuel Arostegui man...@tuenti.com escribió:
2014/1/17 Richard Reina gatorre...@gmail.com
I have 3 relay MySQL database servers on my small office LAN backing up a
master and 3 more machines backing up each relay (1 each). They are all
replicating all databases and all
I have 3 relay MySQL database servers on my small office LAN backing up a
master and 3 more machines backing up each relay (1 each). They are all
replicating all databases and all tables. The master although running fine
is almost eight years old. I'm thinking it's probably time to make one of
the
To activate log-slave-updates do I just add log-slave-updates to the
my.cnf file?
2013/4/30, Manuel Arostegui man...@tuenti.com:
2013/4/30 Richard Reina gatorre...@gmail.com
I have a few slaves set up on my local network that get updates from
my main mysql database master. I was hoping
.
Thanks for the help thus far.
2013/4/30, Manuel Arostegui man...@tuenti.com:
2013/4/30 Richard Reina gatorre...@gmail.com
I have a few slaves set up on my local network that get updates from
my main mysql database master. I was hoping to turn one into a master
while keeping it a slave so
Perfect! Thank you Larry et all.
Have a great weekend.
2013/4/19 Larry Martell larry.mart...@gmail.com
On Fri, Apr 19, 2013 at 8:24 AM, Richard Reina gatorre...@gmail.com
wrote:
Hello All,
Happy Friday! I know how to do the following query:
select count(*) from sales where WEEK
Hello All,
Happy Friday! I know how to do the following query:
select count(*) from sales where WEEK(sale_date)=15 AND
YEAR(sale_date)=2013;
But can someone tell me I can do a query that will give me:
the count(*) for each week of 2013 so that I end up with:
WEEK | COUNT
1 | 22
2
I am looking to spec out hardware for a new database server. I figured
a good starting point would be to find out how much usage my current
server is getting. It just a local machine that runs mysql and is
queried by a few users here in the office. Is there a way that mysql
can tell me info about
use DBI;
my $dbh = DBI-connect( DBI:mysql:rushload;192.168.0.1, $usrr, $passw, {
RaiseError = 3 } );
my $dbs = $dbh-selectcol_arrayref(show databases);
#my $dsn = dbi:mysql:information_schema:192.168.0.1:3306;
#my $dbh = DBI-connect($dsn, $usrr, $passw);
my $dbs = $dbh-selectcol_arrayref('show
I did a GRANT REPLICATION SLAVE ON *.* TO 'user'@'192.168.0.23' IDENTIFIED
BY 'psswd';
on the master. Doesn't *.* mean everything? Why would it just show me to
databases?
2013/4/2 Larry Martell larry.mart...@gmail.com
On Tue, Apr 2, 2013 at 11:30 AM, Richard Reina gatorre...@gmail.com
I have a table like this:
|ORDERS|
|ID| DATE | QNT | LEAD |
|342 | 8-12-12 | 32 | F|
|345 | 8-15-12 | 12 | S|
|349 | 8-16-12 | 9 | R|
I am looking for a way to query it with counts by the LEAD column in
order to tell what the number of each type lead
I have a couple of mysql database slaves and would like to make one of them
be a master as well so that I can set another machine to replicate from it.
Can anyone tell me how I should go about it or know of any howtos for this
specific task?
Thanks,
Richard
I am trying to setup a new slave server and when I go to the master to
copy over master master.info and relay-log.info they seem to be
missing? Not in /var/lib/mysql and could not find it with #: find /
-name master.info
The master server has been setup for years and already has three
slaves
When I do the following query:
SELECT * FROM geo_trivia WHERE city IS NULL;
certain columns that DO have 'NULL' value for city and not a '' (blank)
value do not show up.
I have even gone to the extent of reseting these records value as ='NULL'
with UPDATE and they are still are not selected
-
From: Richard Reina [mailto:gatorre...@gmail.com]
Sent: Friday, March 09, 2012 4:24 PM
To: mysql@lists.mysql.com
Subject: query problem with null
When I do the following query:
SELECT * FROM geo_trivia WHERE city IS NULL;
certain columns that DO have 'NULL' value for city
to write code
that will select a query?
Thanks you,
Richard Reina
I want to create a US geography database. So far I have categories such as
state nick names (some states have more than one), state mottos (text 25 to
150 characters), state name origins (100-300 characters), state trivial
facts, entry into union. My question is; would it be better to keep at
Thank you very much for all the insightful advice. I will keep the
separated.
2011/9/19 Jerry Schwartz je...@gii.co.jp
-Original Message-
From: Richard Reina [mailto:gatorre...@gmail.com]
Sent: Monday, September 19, 2011 9:55 AM
To: mysql@lists.mysql.com
Subject: table design
Message-
From: Richard Reina [mailto:gatorre...@gmail.com]
Sent: Thursday, February 10, 2011 3:07 PM
To: mysql@lists.mysql.com
Subject: function to limit value of integer
Is there a function that can limit the value of an integer in a MySQL
query? I am trying to write a query that scores
Is there a function that can limit the value of an integer in a MySQL
query? I am trying to write a query that scores someones experience.
However, number of jobs can become overweighted in the the query below. If
someone has done 10 jobs vs. 1 that's a big difference in experience. But
someone
I currently have a query that organizes search results for volunteers that
should be called for projects based on how close they live to a project the
and there past attendance.
Currently doing
SELECT name, city, state, phone, prods_done, cancels, miles
FROM volunteer_search
WHERE project_id =
--
Richard Reina
Rush Logistics, Inc.
Watch our 3 minute movie:
http://www.rushlogistics.com/movie
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Thank you very much for all the insightful replies. I think I can get it to
work with a join.
Joerg Bruehe joerg.bru...@sun.com wrote:
Hi!
Jay Blanchard wrote:
[snip]
I have a table similar to this:
-
|transactions |
|ID |DATE
I have a table similar to this:
-
|transactions |
|ID |DATE |EMPLOYEE|
|234 |2010-01-05| 345|
|328 |2010-04-05| 344|
|239 |2010-01-10| 344|
Is there a way to query such a table to give the days of the year that employee
344 did not
I have a perl script that periodically reads and enters (via perl-DBI parsed
system output (about received faxes) into a table. Since the old output is
repeated I have put, when creating the table, the UNIQUE key on the field of
the faxname, which is always different (something like
I am trying to write a query that merges 2 columns from different tables and
show them as one column of data. Something like the following.
payables
ID |check_no| amount|
3 |3478| 67.00 |
4 |3489| 98.00 |
8 |3476| 56.00 |
paychecks
ID |check_no| amount
23 |3469|498.00
I was wondering if someone could lend a hand with the following query. I have
table.
SEARCHES
|ID |trans_no|comp_id|result
13 | 455| 675| o
15 | 302| 675| o
16 | 455| 675| o
12 | 225| 629| y
SELECT count(*) FROM SEARCHES WHERE comp_id=675 AND result='o'
Hello All,
I can't get trim to trim the blank space from a TEXT field in the query below
and was wondering if someone could tell what I am doing wrong?
SELECT TRIM(notes) FROM work_notes;
Thanks for any help as I am at a complete loss.
Richard
--
MySQL General Mailing List
For list
I have a database table paycheck like this.
empno, date, gross, fed_with
1234 2007-09-01 1153.85 108.26
1323 2007-09-01 461.54 83.08
1289 2007-09-01 1153.85 94.41
1234 2007-09-15 1153.85 108.26
1323 2007-09-15 491.94 87.18
1289 2007-09-15 1153.8594.41
I can easily do
I am trying to update from one table to another but I get a syntax error when I
try:
UPDATE from maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE
o.ID=a.ID;
If update does not support aliases, is there another way to do this query? I
am usin V3.23.54. Any help would be
I am a novice when it come to queries such as this and was hoping someone
could help me write a query that tells me how many records have the same ID and
vendor number.
|ID | vendor_no | date|
|2354 | 578 | 2005-12-23|
|2355 | 334 | 2005-12-24|
I's so sorry. You are very correct. The sample data is bad. ID should be
unique. Here it is corrected.
|ID| vendor_no| date|
|2354 | 578 | 2005-12-23|
|2355 | 334 | 2005-12-24|
|2356 | 339 | 2005-12-26|
|2357 | 339
table_name_here
GROUP BY ID, vendor_no
HAVING dupes 1;
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Richard Reina [EMAIL PROTECTED] wrote on 02/23/2006 12:49:28 PM:
I's so sorry. You are very correct. The sample data is bad. ID
should be unique. Here it is corrected
)) as recent_tx
FROM transactions_table
GROUP BY c_no
HAVING total_tx 4 and recent_tx = 0;
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Rhino [EMAIL PROTECTED] wrote on 01/05/2006 10:43:15 AM:
- Original Message -
From: Richard Reina [EMAIL PROTECTED
|23.00|
Obviously my table has many more entries.
Thank you for any help.
Sincerely,
Richard Reina
A people that values its privileges above its principles soon loses both.
-Dwight D. Eisenhower.
3.23.54
Thanks.
Rhino [EMAIL PROTECTED] wrote:
- Original Message -
From: Richard Reina
To:
Sent: Thursday, January 05, 2006 10:29 AM
Subject: SELECT help.
Can someone help me write a query to tell me the customer numbers (C_NO)
of those who've had more than 4 transactions
I am trying to get this query to randomly pick a
record from a table (trans) but it's not working:
SELECT ID FROM trans ORDER BY RAND() LIMIT 1;
Can anyone let me know what I'm doing wrong and/or how
to fix it.
Thanks,
Richard
--
MySQL General Mailing List
For list archives:
Dear MySQL Developers and Enthusiasts,
when I run these lines of code :
my $T_NO = 12569;
use DBI;
my $dbh =
DBI-connect(DBI:mysql:database=carr_search;192.168.0.1,user,password);
my $q = CREATE TABLE IF NOT EXISTS CS_? (
ID_NO MEDIUMINT,
NAME VARCHAR(30),
TYPE CHAR(1)
);
my $sth =
I am having trouble with a query that gives me
activities that have not been written up but if
these activities are a party they whould only appear
in the query if they have been held, hence date will
not='-00-00'
If I write the query as so, no non-party activities
will show up because the
I know to most of you this will seem like a mundane
question, but I was hoping someone can tell me how to
select the last record in a table that meets certain
criteria. Like to see who hosted the last party in
CHicago.
SELECT host FROM PARTY
WHERE city=chicago;
PARTY
ID |host | city | st |
Data from my business's transactions are stored in a
few dozen tables in about 5 databases. Right now the
MySQL server with dual RAID 0 80 gig drives. So far
the data which stores about 4 years worth of data only
takes up 1 gig. There are about 4 or five work
stations that store data on this
Anyone know why this query takes so long?
SELECT SUM(l.cost+l.fscc)
FROM orders o, acctg.invoice i
LEFT JOIN acctg.payable p
ON (o.ORD_NO=p.ORD_NO)
WHERE p.ORD_NO IS NULL
AND i.ORD_NO=o.ORD_NO;
Is there something I can do to speed it up?
Thanks,
Richard
--
MySQL General Mailing List
For
Is it possible to do two left joins involving three
tables in one query?
select a.id, a.amount FROM t1
LEFT JOIN t2 ON (t1.id=t2.id)
then
LEFT JOIN t3 ON (t1.id=t3.id)
Is this even possible?
Any help would be greatly appreciated.
Richard
--
MySQL General Mailing List
For list archives:
Thank you very much Shawn and Mike for your quick responses. Left join
was exactly what I was looking for and it worked quite nicely.
Once again, thanks for your help.
Richard
[EMAIL PROTECTED] wrote:
Richard,
This is the case for using a LEFT JOIN. You want everything from the left
table
I have two tables:
EVENT
IDname date sponsor_ID
23 Sady Hawkins 2004-11-04 235
89 Founders Day 2004-12-21 NULL
87 Winter Gala 2004-01-23 NULL
SPONSOR
ID name
I would like to backup databases from a linux MySQL server to another
linux machine on the same private network but I don' see in the docs how
I can do this with mysqlhotcopy or mysqldump. Is there any way to do
this besides using ftp.
Any help would be appreicated.
Richard
--
MySQL General
I am wanting to protect myself against future potential hard drive
failures on my DB server running version 3.23.49a. Should I try and set
up a RAID, a mirror or would the best solution be to set up MySQL
replication. Any suggestions would be greatly appreciated.
Richard
--
MySQL General
I am wanting to protect myself against future potential hard drive
failures on my database server running version 3.23.49a. Should I try
and set up a RAID, a mirror or would the best solution be to set up
MySQL replication. Any suggestions would be greatly appreciated.
Richard
--
MySQL
Benjamin Pflugmann wrote:
Hello.
On Sat 2002-12-07 at 14:15:59 -0800, [EMAIL PROTECTED] wrote:
I would like to lock a specific record in a MySQL table so that no other
user can update it while another user is is update the record via the
user interface. Can someone please tell me the
I would like to lock a specific record in a MySQL table so that no other
user can update it while another user is is update the record via the
user interface. Can someone please tell me the easiest way to do this?
Thank you,
Richard Reina
I was wondering if someone could help me out with the syntax to use
mysqlhotcopy to backup a database to a tape drive ( /dev/nst0 ). I've
tried a few variations of the command but with no luck so far. The
mysql server is running on RH 7.2 and the DAT tape drive is on that same
machine.
I was wondering if someone could help me out with the syntax to use
mysqlhotcopy to backup a database to a tape drive ( /dev/nst0 ). I've
tried a few variations of the command but with no luck so far. The
mysql server is running on RH 7.2 and the DAT tape drive is on that same
machine.
I was wondering if someone could help me out with the syntax to use
mysqlhotcopy to backup a database to a tape drive ( /dev/nst0 ). I've
tried a few variations of the command but with no luck so far. The
mysql server is running on RH 7.2 and the DAT tape drive is on that same
machine.
I have downloaded mysql 3.23.49a source. When I run ./configure
--without-server it hangs. After several minutes I interupt it with
Cntrl - C. No makefile has been created neither has config.cache. I
then try ./configure --help and the exact same thaing happens. Can
anyone give an idea
I am not sure what the heck the problem is. I'm trying to install mysql
(client) on a RH 7.2 box. When I run ./configure --without-server I get
a bunch of messages about mysql passwords being changed and mysql daemon
being started -- sorry I can't include the exact messages but I have no
I am not sure what the heck the problem is. I'm trying to install mysql
(client) on a RH 7.2 box. When I run ./configure --without-server I get
a bunch of messages about mysql passwords being changed and mysql daemon
being started with databases from /install directory -- sorry I can't
if you utilize this API, you will not need to install
MySQL. I am just guessing however, as I have no knowledge of Perl. Good
Luck.
Regards,
John
http://mysql.turbolift.com/mysql/DBD_3.21.X.php3
- Original Message -
From: Richard Reina [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday
In order to run perl scripts that use DBI ( from a cleint machine ) to
query a MySQL server on the LAN Do I need to install MySQL on the client
machine or do I just need DBI? Both machines are running Red Hat 7.2.
Any help would be appreciated.
Richard
I am upgrading from MySQL ver. 3.22.32 to 3.23.41. I have installed
ver. 3.23.41 on a new server which I will eventually use to replace my
old server that's running 3.22.32. I have tar'ed and ftp'ed the
datbases files over to the new server. When I copy them over to the
table directory (
You're stumped!? Where does that leave us? Unfortunately (AFAIK) the sample data you
have enclosed does not
demonstrate the condition you seek to describe - perhaps it would have been better if
you gave us a replicable
example, so that we can UNDERSTAND the problem before we try to help you
I'm stumped -- which isn't saying much because I'm hardly a scientist.
I am struggling to write a query that will tell me how many times a new
volunteer ( defined as a volunteer who has never worked a shift ) did
not show up for his shift (first shift). From my data I would
eventually like to
I am trying to trim both leading and trailing whitespace from a TEXT
column in my SELECT query. I found the TRIM() command in the manual,
however, I can't get it to act upon a column name instead of an
actual string. Can anyone help?
If you just say TRIM(col_name) it will return the
I am trying to trim both leading and trailing whitespace from a TEXT
column in my SELECT query. I found the TRIM() command in the manual,
however, I can't get it to act upon a column name instead of an
actual string. Can anyone help?
If you just say TRIM(col_name) it will return the
I am trying to trim both leading and trailing whitespace from a TEXT
column in my SELECT query. I found the TRIM() command in the manual,
however, I can't get it to act upon a column name instead of an actual
string. Can anyone help?
Thanks,
Richard
many characters. If it did change
something I fear it would change all of the text in the columns to 'No.'
which would send me into a rage. If I am missing something please let
me know. And if anyone else might know how I can solve this dilema, I
would appreciate the input.
Richard Reina
I have a table shpr_rcvr that stores info about locations where things
are shipped to and from. Each record has a text field called DIRECTIONS
that contains directions on how to get to the location. Often in the
directions column the # sign is used to help describe and exit number
off the
I am running mysql on a linux box RH 6.2. I backup all the filesytems
with:
/sbin/dump 0ubf 800 /dev/npt0
to a COLORADO 8gig. travan tape drive. Is this sort of backup regimen a
good one -- compatible with MySQL -- or should I instead be backing up
with FLUSH and (or) mysqldump?
Any
I have a perl-DBI-MySQL database app. that handles everything from my
order entry to my accounts payable. I am in the process of cleaning up
a lot of the code I've written to make it easier to maintain. I
probably use the following DBI staement handle about fifty different
places on
I accidently overote a table in my database. I need to restore an
individual table from a backup that I made on tape
drive with dump. How can I restore just the individual table with
restore -i.
Richard
-
Before posting,
Thank you very much for the reply. I'll give it a try.
Richard
Jeremy Zawodny wrote:
On Mon, Jun 04, 2001 at 04:50:03PM -0700, Richard Reina wrote:
I accidently overote a table in my database. I need to restore an
individual table from a backup that I made on tape drive with dump
I am stuck on a select query and was wondering if someone could help. I
have I've written a database app. that helps me run my business
(trucking). I need however to write a query that shows me all of the
loads that are delivered but not billed (invoiced). Which means that I
have to select the
Thank you to all who responded. A left join was definately the answer.
Richard
Roger Karnouk wrote:
Try this
SELECT l.load_no l.date FROM loads l left join invoice i
on (l.load_no = i.load_no)
where i.load_no is null
AND l.dlvr_date 0;
-Original Message-
From: Richard
the way to where they went to high school could be
stored in one table logically or more importantly efficiently.
-Original Message-
From: ryc [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 11, 2001 11:58 AM
To: 'Richard Reina'; [EMAIL PROTECTED]
Subject: Re: Hom many columns is too
I am designing an in house database app. for sales/contact management.
We've identified out about 75 things to know about a customer/contact
from there name all the way to where they went to highschool. Should
all these attributes be in one table since they all describe the contact
or should
DateDB_amount CR_amount
2001-04-09 NULL300.00
2001-04-09 NULL750.00
2001-04-09 NULL300.00
Anyone know how can I do a query that will update the third record
without updating the first?
to only change one record.
Richard Reina wrote:
DateDB_amount CR_amount
2001-04-09 NULL300.00
2001-04-09 NULL750.00
2001-04-09 NULL300.00
Anyone know how can I do a query that will update the third record
without
I am try to do a join with tables that are in two different databases.
I thing I've got the actual SQL syntax down:
my $q = "SELECT i.inv_no, i.inv_date,
c.cust_name
FROM receivables.invoice i, sales.customer c,
WHERE i.paid_date IS NULL";
but I am confused on how to
Here is the table:
+--+
| STOP_0FFS |
+--+
|FLIGHT_NO |CITY | SEQUENCE|
|127 |Chicago |1|
|127 |Boston |2|
|391 |Miami|1|
+--+
SELECT
How do you decide when a table should go into another database. For
example, I have a database with about 12 tables and growing. I keep
them in one database because I am doing joins that periodically involve
all of the tables. Is this the right way to do it?
Richard
PUB TABLEEVENT TABLE
|p_id|pub_name |address ||eventid|date
|host_id|
|13 |John Barleycorn's |1800 N. Lincoln ||321|2001-01-27 |
32|
|26 |Harry Carry's |300 N. Dearborn ||198|2001-02-21 |
Thanks for your reply. Believe I'm thristy two and if I ever get this
thing to work I'm going to pub crawl all the way home.
What I am trying to do is display the beginning and ending points for
each pub crawl effedtively leaving out any intermediate stops so that
the summary of the pub craw
I run a small business. A year and a half ago I embarked on a jorney to
port my DOS Foxpro Database to linux. I selected mysql as an the engine
and and begand writing the app. using perl/dbi and perlmenu
module/curses as the front end at first I used NFS to share the databse
files on the LAN.
n Wed, Jan 17, 2001 at 03:17:52PM -0600, Richard Reina wrote:
I run a small business. A year and a half ago I embarked on a
jorney to port my DOS Foxpro Database to linux. I selected mysql as
an the engine and and begand writing the app. using perl/dbi and
perlmenu module/curses as the
. Zawodny" wrote:
On Wed, Jan 17, 2001 at 03:58:11PM -0600, Richard Reina wrote:
Thanks for the reply. Curses worked OK. But I was convinced that a
web server would be more efficient than NFS for sharing the database
files. Is that true? Or is a NFS a longtime viable solution for
95 matches
Mail list logo