From: "Jon Miller" <[EMAIL PROTECTED]>
To:
Subject: Problem with query
Date: Tue, 24 May 2005 19:40:32 +0800
Quite new to MySQl and queries in general and would like some help in the
following query:
select prDate, prName, prLEmployee, prDescription, prTotalHours, prLStatus,
prCompletionDate,
From: [EMAIL PROTECTED]
To: "shaun thornburgh" <[EMAIL PROTECTED]>
CC: mysql@lists.mysql.com
Subject: Re: 2 Joins in 1 Query
Date: Mon, 23 May 2005 12:20:05 -0400
"shaun thornburgh" <[EMAIL PROTECTED]> wrote on 05/23/2005
10:18:29 AM:
> Hi,
>
> T
Hi,
Unfortunately that doesnt work, I need to LEFT JOIN Bookings to Weeks but
keep the current join on Projects there as well, any ideas?
From: "Peter Normann" <[EMAIL PROTECTED]>
To: "'shaun thornburgh'"
<[EMAIL PROTECTED]>,
Subject: RE: 2 Joins
Hi,
The following query produces the number of bookings per project grouped by
week:
SELECT WEEK(Booking_Start_Date) AS "WEEK",
SUM(IF(B.Project_ID = "23", 1,0)) AS `Project A`,
SUM(IF(B.Project_ID = "42", 1,0)) AS `Project B`
FROM Bookings B INNER JOIN Projects P USING (Project_ID)
WHERE B.Bo
Hi,
Is it possible to update all the domain names for an email column? I want to
change everyones email address to my domain for a test site, so can I change
the email address column such that everything after @ is changed to
mydomain.com?
Thanks for your advice
--
MySQL General Mailing List
F
Hi,
I am getting an error on the following query and but can't understand why,
the syntax looks fine to me!
mysql> UPDATE CSV_Upload_Data SET SUBSTRING(PRACT_ASCII, 16, 6) = '11'
WHERE Insertion_ID = 190716;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that cor
Hi,
I keep getting thw following error when i include this line:
AND COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) > 1
mysql> SELECT PRACT_NUMBER,
-> COUNT(SUBSTRING(PRACT_NUMBER, 1, 4))
-> FROM CSV_Upload_Data
-> WHERE CSV_File = 'ICS'
-> AND CHAR_LENGTH(PRACT_NUMBER) > 4
-> AND COUNT(SUBSTR
Hi,
is it possible to alter fields in a column by removing the first three
characters?
i.e. change 100123456789 to 123456789
Thanks for your help
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
2 | Day 2 |
+--++--+---+
2 rows in set (0.00 sec)
mysql>
Do you see my problem here?! Any advice would be greatly appreciated...
Shaun
From: [EMAIL PROTECTED]
To: "shaun thornburgh" <[EMAIL PROTECTED]>
CC: mysql@lists.mysql.com
Subject: Re: How can
gt;
Here you can see that Practice 11049 has four bookings. The two for project
32 are ok, but the two for project 3 do not have a booking where the day
type is 1.
I hope this makes sense! Thanks again for your help.
Shaun
From: "Jay Blanchard" <[EMAIL PROTECTED]>
To: "sh
Hi,
I have four tables among others in my database: Bookings, Work_Types,
Practices & Projects. Bookings occur in a Practice for a Project and have a
Work_Type. A Practice can have many Bookings but must have one and only one
Booking where the Work_Type.Day_Type = 1. This rule was introduced aft
Hi,
I am trying to create a query that returns a users availability for the next
ten days using version 4.1.11. A user has 8.5 hours available per day so a
users availability is equal to 8.5 less any bookings they have on that day.
I have a table called Bookings (see below), and each 'Booking' h
Hi,
I have a table called Bookings which holds start times and end times for
appointments, these are held in Booking_Start_Date and Booking_End_Date. I
have a page on my site that runs a query to produce a grid to show
availiability per day for the next ten days for each user of the system.
Use
Hi,
I have a DATETIME COLUMN in my table and i need to be able to perform an
update on all colmns that are 48 hours old as specified in that column. As I
am using 4.0.17 I am unable to use the TIME() functions, is there another
way to do this?
Thanks for your help
--
MySQL General Mailing List
Hi,
I have ( among others ) three tables in my database: Claims, Expenses and
Mileage. A claim can contain many expense entries and many mileage entries.
I am using the follwing query to show the total expenses and mileage per
claim for a particulare user:
SELECT C.*, SUM(E.Amount) AS Amount, S
Hi,
Is it possible to change the order of columns in a table after the table has
been created?
Thanks for your help
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hi,
I have a web based that allows users to upload XML files and insert them
into the database, however some of these files are large and are causing the
webserver to timeout.
Is it possible to create a cron job that can insert the xml files say every
hour, then the users of the site would only
Hi,
Is it possible to use the LOAD DATA INFILE to load an XML file into a table?
Thanks for your help
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
From: "Rhino" <[EMAIL PROTECTED]>
To: "shaun thornburgh"
<[EMAIL PROTECTED]>,
Subject: Re: Help with a query please
Date: Mon, 21 Feb 2005 08:22:29 -0500
- Original Message -
From: "shaun thornburgh" <[EMAIL PROTECTED]>
To:
Sent: Monday
Hi,
I am having trouble with the following query:
SELECT U.User_ID,
U.User_Firstname,
U.User_Lastname
FROM Users U, Allocations A
WHERE (U.User_ID = A.User_ID OR U.User_ID = 101)
AND A.Project_ID = '12'
AND ( U.User_Type = 'Staff'
OR U.User_Type = 'Manager'
OR U.User_Type = 'Administrator' )
ORDER
No just every row needs to be unique. Sorry for the confusion...
From: "Robert Dunlop" <[EMAIL PROTECTED]>
To: "shaun thornburgh"
<[EMAIL PROTECTED]>,
Subject: Re: LOAD DATA INFILE using 4.0.17
Date: Tue, 15 Feb 2005 15:06:19 -0800
So what you meant was every
Hi,
Thanks for your reply, but the problem I am facing is that there may be
duplicate values in the uploaded file and I dont want these to appear in my
table...
From: Bastian Balthazar Bux <[EMAIL PROTECTED]>
To: shaun thornburgh <[EMAIL PROTECTED]>
Subject: Re: LOAD DATA INFILE
Hi,
I have a table with 26 fields, each row in this table must be unique. I
can't define all of the fields to be primary keys as the limit is 16.
Therefore before I insert data I have to check that each row is unique. Data
is to be inserted into this table from files uploaded to the server - CSV
Hi,
The following function loads data from a file:
http://dev.mysql.com/doc/mysql/en/load-data.html
Is there a function like this that I can use to save the results of a query
to a CSV file for the user of my PHP application to donwload?
Thanks for your help
--
MySQL General Mailing List
For lis
Hi,
Is it possible to add an ENUM column to a table after it has been created?
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
_DATA_ENC_FORENAME, "test_password" ) FROM DATA_TABLE_PID_
mysql>
Any ideas?
From: Jeff Smelser <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Subject: Re: ENCODE & DECODE
Date: Fri, 4 Feb 2005 10:20:14 -0600
On Friday 04 February 2005 09:51 am, shaun thornburgh wrote:
> I have a table
Hi,
I have a table where users upload various data items via a web site. Some
fields in the table are named DATA_ENC... to denote that the data should be
encrypted uusing the encode function. This all works fine, however when I
come to selecting the data from the table I would like to be able to
his?
From: "Gabriel PREDA" <[EMAIL PROTECTED]>
Reply-To: "Gabriel PREDA" <[EMAIL PROTECTED]>
To: "shaun thornburgh"
<[EMAIL PROTECTED]>,
Subject: Re: SHOW TABLES Problem
Date: Wed, 2 Feb 2005 13:48:43 +0200
Because it does not know REGEXP... only LIKE patter
Hi,
The syntax for show tables from the manual is:
SHOW [FULL|OPEN] TABLES [FROM db_name] [LIKE 'pattern']
so whay doesnt the following query work:
SHOW TABLES LIKE REGEXP 'PID_[0-9]+';
Thanks for your help
Shaun
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsu
Hi,
I get very nervous when I log onto my database via SSH and type in queries
manually. Take the following query for example:
Delete FROM Users WHERE User_ID = 5;
If I hit return before I start typing WHERE then things would be
disastorous! Is there any type of undo function with MySQL?
Thanks
Hi,
I am trying to get all field names from my table that begin with
letter X or Y, however the following statement and many variations I
have tried produce an error:
SHOW COLUMNS FROM TABLE LIKE "X%" OR LIKE "Y%"
Any help here would be much appreciated :)
--
MySQL General Mailing List
For list ar
Hi,
I am trying to get all field names from my table that begin with letter X or
Y, however the following statement and many variations I have tried produce
an error:
SHOW COLUMNS FROM TABLE LIKE "X%" OR LIKE "Y%"
Any help here would be much appreciated :)
--
MySQL General Mailing List
For list
Hi Guys,
Thanks for your replies, i have also found SET which appears to do the same
thing, is there a reason why everyone suggested ENUM as opposed to SET?
Thanks for your help
From: Mattias J <[EMAIL PROTECTED]>
To: "shaun thornburgh" <[EMAIL PROTECTED]>, mysql@lists.mysq
Hi,
I am attempting to create a database and have a query. Is it possible for
mysql to contain the possible field values in the database rather than
creating the options in the application?
For example Field_A can only be Yes or No, Field_B can be 50% or 80% etc.
I feel that creating the applica
Hi,
The following query returns the amount of hours a user has been booked for
on a particular day.
SELECT (SUM(((DATE_FORMAT(B.Booking_End_Date, "%k") * 60 ) +
DATE_FORMAT(B.Booking_End_Date, "%i")) -
((DATE_FORMAT(B.Booking_Start_Date, "%k") * 60 ) +
DATE_FORMAT(B.Booking_Start_Date, "%i")))
A.Project_ID = 11
ORDER BY User_Firstname;
Thanks,
Shaun
From: [EMAIL PROTECTED]
To: "shaun thornburgh" <[EMAIL PROTECTED]>
CC: mysql@lists.mysql.com,[EMAIL PROTECTED]
Subject: Re: Help with a join query please!
Date: Wed, 29 Dec 2004 15:58:43 -0500
"shaun thornburgh" <[EMA
the Tasks Project_ID.
This is what I need to do:
UPDATE Bookings B
SET B.Task_ID = T.Task_ID
WHERE T.Task_Name = 'Booking'
AND B.Project_ID = T.Project_ID
However I dont know how to introduce the Tasks T alias into the query!
Thanks for your help,
Shaun
From: "Jay Blanchard"
ticular project.
Thanks again for your help!
From: [EMAIL PROTECTED]
To: "shaun thornburgh" <[EMAIL PROTECTED]>
CC: mysql@lists.mysql.com,[EMAIL PROTECTED]
Subject: Re: Help with a join query please!
Date: Thu, 23 Dec 2004 10:14:24 -0500
So -- what's the field that relates a boo
Hi,
I am having trouble with an UPDATE query. I have three tables as defined
below. My database holds data for bookings. Each booking relates to a
project. Each project has many tasks, one of which is a booking. I have now
added the column Task_ID to the bookings table. How can I update all the
Hi,
Thanks for your reply but that produces exactly the same result...
Any ideas?
From: Sasha Pachev <[EMAIL PROTECTED]>
To: shaun thornburgh <[EMAIL PROTECTED]>
CC: mysql@lists.mysql.com
Subject: Re: Help with a join query please!
Date: Tue, 21 Dec 2004 14:57:43 -0700
shaun thornbur
Hi,
I have (among others) three tables in my database that i am struggling with
a join query on. The database holds dates for Bookings. If Users are
Allocated to a particular Project they can be booked. However if a user is
booked but then unallocated I want to be able to display all peolple
al
From: "Rhino" <[EMAIL PROTECTED]>
To: "shaun thornburgh"
<[EMAIL PROTECTED]>,<[EMAIL PROTECTED]>
Subject: Re: Help with a join query please
Date: Thu, 16 Dec 2004 14:59:48 -0500
----- Original Message -
From: "shaun thornburgh" <[EMAIL
Hi,
I have (among others) three tables in my database that i am struggling with
a join query on. The database holds dates for Bookings. If Users are
Allocated to a particular Project they can be booked. However if a user is
booked but then unallocated I want to be able to display all peolple
al
Hi,
The following query works out the amount of hours a user has been booked for
on a particular day:
SELECT
(SUM(((DATE_FORMAT(B.Booking_End_Date, "%k") * 60 ) +
DATE_FORMAT(B.Booking_End_Date, "%i")) -
((DATE_FORMAT(B.Booking_Start_Date, "%k") * 60 ) +
DATE_FORMAT(B.Booking_Start_Date, "%i")))
Hi,
I am creating an online timesheet application. Most parts are done, however
I have a problem displaying a list of unapproved timesheets.
Here is my timesheet table:
mysql> DESCRIBE Timesheets;
+---+-+--+-+++
| Field | T
Hi,
I have a table called Bookings which holds information for bookings(!) for
staff members in my database:
mysql> DESCRIBE Bookings;
+---+-+--+-+++
| Field | Type| Null | Key | Default| Extra
Hi,
Thanks for your reply but the result isnt quite right!!! :
mysql> SELECT T.Date, T.Hours, P.Project_Name FROM Timesheets T
-> LEFT JOIN Projects P ON (P.Project_ID = T.Project_ID and T.Date >=
-> '2004-10-18'
-> AND T.Date <= '2004-10-24')
-> WHERE T.User_ID = "1"
-> AND T.Type =
Hi,
I have query that I am using to try to get the number of hours worked and on
which project by an employee during a particular week. Here is the query:
SELECT T.Date, T.Hours, P.Project_Name
FROM Timesheets T
LEFT JOIN Projects P ON P.Project_ID = T.Project_ID
WHERE T.Date >= "2004-10-18"
AND
Hi,
I have the following two tables in my database:
mysql> DESCRIBE Projects;
+--+--+--+-+-++
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-++
| Pro
Hi,
Is it possible to join two fields in a query so that they are displayed as
one column? For exmaple:
SELECT Firstname + ' ' + Lastname AS 'Name' FROM Users;
I hope you can see what I am trying to achieve from SQL here!
Thanks for your help
__
Hi,
In my Users table I have one column for users names, I need to modify this
now so that users have a column for first names and a seperate column for
last names. My question is how can I update the current users in the table
so that their details are stored correctly?
Many thanks for your he
Hi,
I am having trouble with a LEFT JOIN. I have three tables: Users, Projects
and Allocations. Users can be allocated to zero or more projects and a
project can be allocated to zero or more users. Table descriptions are
below.
How can i select a users details and all of the pojects they are al
Hi,
I use the following query to extract information about a practice in my
database. However if the practice system id hasnt been set then the query
won't work.
SELECT P.*, S.System_Name FROM Practices P, Systems S WHERE P.Practice_ID =
'".$_SESSION['ses_practice_id']."' AND S.System_ID = P.Sy
Hi,
I have a table called Bookings which holds start times and end times for
appointments, these are held in Booking_Start_Date and Booking_End_Date. I
have a page on my site that runs a query to produce a grid to show
availiability per day for the next ten days for each user of the system.
Use
Hi,
I have a table called Bookings which holds start times and end times for
appointments, these are held in Booking_Start_Date and Booking_End_Date. I
have a page on my site that runs a query to produce a grid to show
availiability per day for the next ten days for each user of the system.
Use
Hi,
Using the following query I am attampting to insert the Work_Type_IDs from
Work_Types WHERE Project_ID = 'x'. However I also wan to insert the
Project_ID into the table, how would this syntax work - apperently the
column count is incorrect...
INSERT INTO Letter_Templates (Work_Type_ID, Proj
L PROTECTED]
To: "shaun thornburgh" <[EMAIL PROTECTED]>
CC: [EMAIL PROTECTED]
Subject: Re: A Complicated Group Query
Date: Tue, 22 Jun 2004 14:32:14 -0400
OOPS! That's because my second query should have been a LEFT JOIN and not a
RIGHT JOIN (I hate making silly cut-and-paste error
s!
I hope you can help me here, many thanks!
From: [EMAIL PROTECTED]
To: "shaun thornburgh" <[EMAIL PROTECTED]>
CC: [EMAIL PROTECTED]
Subject: Re: A Complicated Group Query
Date: Tue, 22 Jun 2004 10:12:22 -0400
Hi Shaun,
You said:
>SELECT COUNT(B.Booking_ID), U.User_Location
Hi,
I have three tables (among others!) in my database; Bookings, Users and
Projects. Users have location codes and can make bookings which are for a
particular project.
Using the following query I can get a list of all Locations (Each user has a
Location code) and the number of Bookings that h
s query is returning all of the users in the user table regardless of the
Project_ID...
So what I need is a query that returns a count of bookings for all user
locations where the Project_ID = 'X'
Thanks again for your help
From: [EMAIL PROTECTED] (Johan Hook)
To: shaun thornburgh
_ID GROUP BY(U.User_Location);
God Bless
Paul C. McNeil
Developer in Java, MS-SQL, MySQL, and web technologies.
GOD BLESS AMERICA!
To God Be The Glory!
-----Original Message-
From: shaun thornburgh [mailto:[EMAIL PROTECTED]
Sent: Friday, June 18, 2004 7:54 AM
To: [EMAIL PROTECTED]
Subject: Group
Hi,
The following table produces a query that shows all bookings that user has
made and groups the number of bookings by the users location code.
mysql> SELECT COUNT(B.Booking_ID), User_Location FROM Bookings B, Users U
WHERE U.User_ID = B.User_ID GROUP BY(U.User_Location);
At the momnet if no
Hi,
I have two tables in my database. One that holds information on bookings on
varous projects for a scheduling system, and the other holds project
informtaion.
Is it possible to produce a report that lists total bookings by project a
month with one query i.e.
Jan Feb Mar Apr
5 4 5 7 125 1
Project 3 8 7 1 9 2141
Thanks for your help
From: "Sunmaia" <[EMAIL PROTECTED]>
To: "shaun thornburgh"
<[EMAIL PROTECTED]>,<[EMAIL PROTECTED]>
Subject: RE: A query that groups information by
Hi,
I have a table that holds information on bookings for a scheduling system.
Is it possible to produce a report that lists total bookings data by month
with one query
i.e.
Jan Feb Mar Apr May Jun Jul
6 7 3 9 2 4 3
Thanks for you help
_
Hi Michael,
Thankyou for your reply, unfortunately I am unable to use InnoDB tables...
From: Michael Stassen <[EMAIL PROTECTED]>
To: shaun thornburgh <[EMAIL PROTECTED]>
CC: [EMAIL PROTECTED], [EMAIL PROTECTED]
Subject: Re: DELETE from more than one table
Date: Tue, 08 Jun 2004 12
00 on 06/07/2004, shaun thornburgh wrote about Re: DELETE from
more than one table:
Hi Paul,
Thanks for the reply.
I actually need to delete data from about 10 tables, is this possible?
If the field in the tables is defined as a Foreign Key (with ON DELETE
CASCADE), deleting the root key will do th
Hi Paul,
Thanks for the reply.
I actually need to delete data from about 10 tables, is this possible?
From: "Paul McNeil" <[EMAIL PROTECTED]>
To: "shaun thornburgh" <[EMAIL PROTECTED]>
Subject: RE: DELETE from more than one table
Date: Mon, 7 Jun 2004 08:4
Hi,
Is it possible to DELETE from more than one table in one command?
Thanks for your help
_
Want to block unwanted pop-ups? Download the free MSN Toolbar now!
http://toolbar.msn.co.uk/
--
MySQL General Mailing List
For list archive
Hi,
I have a problem with a SELECT query. I have a users table and each user can
be a Rep, Nurse or Administrator defined by User_Type. Each Rep has two
Nurses allocated to them and the User_ID of the nurse is held in column
Rep_Nurse_1 and Rep_Nurse_2. I am trying to perform a query that return
Hi,
I am trying to set the mysql variable lower_case_table_names to 2, however I
am having serious difficulties!
I understand I need to do this at the command line but I have tried the
following (with many variations) without success:
C:\mysql\bin>mysqld --SET-lower_case_table_names=2;
Ideall
Hi,
I have just installed MySQL on Windows 2000, after inspecting the mysql
users table I have noticed that there are four users:
Host User
localhost root
gcw root
% root
gcw
I know about the two root accounts but where did the gcw user come from,
there is no documentat
Hi,
I am trying to synchronize two databases - the source is on a FreeBSD web
server and the target is on our local Windows 2000 Server machine. I am
trying to do this using Webyog (www.webyog.com) which tells Windows to sync
with the source machine every hour.
However, this is producing error
Hi,
I have installed mysql 3.23 on our Windows 2000 Server machine. How can I
configure the database such that anyone on the internet can connect to this
database? I know this sounds dangerous but once this has been configured we
will install VPNs on this machine and any machine that we permit
Hi,
I have a table called Bookings which has two important columns;
Booking_Start_Date and Booking_End_Date. These columns are both of type
DATETIME. The following query calculates how many hours are available
between the hours of 09.00 and 17.30 so a user can see at a glance how many
hours th
Hi,
I have a table called Bookings which has two important columns;
Booking_Start_Date and Booking_End_Date. These columns are both of type
DATETIME. The following query calculates how many hours are available
between the hours of 09.00 and 17.30 so a user can see at a glance how many
hours they h
Thanks for your reply, but its not an option!
From: "Dan Greene" <[EMAIL PROTECTED]>
To: "shaun thornburgh" <[EMAIL PROTECTED]>,
<[EMAIL PROTECTED]>
Subject: RE: Help With a DATETIME Query
Date: Tue, 7 Oct 2003 16:41:04 -0400
I know it's not the a
Hi,
I have a table called Bookings which has two important columns;
Booking_Start_Date and Booking_End_Date. These columns are both of type
DATETIME. The following query calculates how many hours are available
between the hours of 09.00 and 17.30 so a user can see at a glance how many
hours they h
Hi,
I have a table called Bookings which has two important columns;
Booking_Start_Date and Booking_End_Date. These columns are both of type
DATETIME. The following query calculates how many hours are available
between the hours of 09.00 and 17.30 so a user can see at a glance how many
hours they h
Hi,
I have a table called Bookings which has two important columns;
Booking_Start_Date and Booking_End_Date. These columns are both of type
DATETIME. The following query calculates how many hours are available
between the hours of 09.00 and 17.30 so a user can see at a glance how many
hours they h
80 matches
Mail list logo