Re: 2 Joins in 1 Query
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, 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.Booking_Type = Booking AND DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) = '2005-01-01' AND DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) = '2005-12-31' GROUP BY WEEK; The problem with this is that if I have no bookings for week 42 for example then that week is not shown in the result. To get round this I created a table called Weeks that contains all the week numbers for the year. However I am not sure how I can join Weeks to Bookings so that all the weeks show. Any healp would be greatly appreciated. TABLE DEF'S: mysql desc Bookings; +-+-+-- +-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+-- +-+-++ | Booking_ID | int(11) | | PRI | NULL| auto_increment | | Booking_Type| varchar(15) | | | Unavailability || | User_ID | int(11) | | | 0 || | Project_ID | int(11) | YES | | NULL|| | Rep_ID | int(11) | YES | | NULL|| | Practice_ID | int(11) | YES | | NULL|| | Booking_Creator_ID | int(11) | YES | | NULL|| | Booking_Creation_Date | datetime| YES | | NULL|| | Booking_Start_Date | datetime| | | -00-00 00:00:00 || | Booking_End_Date| datetime| | | -00-00 00:00:00 || | Booking_Completion_Date | date| YES | | NULL|| | Booking_Mileage | int(5) | YES | | NULL|| | Booking_Status | varchar(15) | | | Other || | Unavailability_ID | int(2) | YES | | NULL|| | Task_ID | int(11) | YES | | NULL|| | Work_Type_ID| int(2) | YES | | NULL|| | Additional_Notes| text| YES | | NULL|| +-+-+-- +-+-++ 22 rows in set (0.00 sec) mysql desc Projects; ++--+--+-+- ++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+- ++ | Project_ID | int(11) | | PRI | NULL| auto_increment | | Project_Name | varchar(100) | | | | | | Client_ID | int(11) | | | 0 | | ++--+--+-+- ++ 8 rows in set (0.00 sec) mysql desc Weeks; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | Week_ID | int(11) | | PRI | NULL| auto_increment | | Week_Number | int(11) | | | 0 || +-+-+--+-+-++ 2 rows in set (0.00 sec) mysql I think you need just think about what you want and what may or may not exist as data, then you can figure out which JOINs are LEFT and which are INNER. You want one row for each week regardless of whether you have a Project or a Booking. That makes the Weeks table manditory. There may be weeks that do not have any Bookings. That makes Bookings the right side of a LEFT JOIN. Because the existence
RE: Problem with query
From: Jon Miller [EMAIL PROTECTED] To: mysql@lists.mysql.com 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, prActive from tProject where prDate =2005-05-09 It generates a listing that has years from 2001 to present. All I'm looking for is information start from 2005-05-09 to present. Thanks TEXT.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Try putting quotes around the date select prDate, prName, prLEmployee, prDescription, prTotalHours, prLStatus, prCompletionDate, prActive from tProject where prDate = '2005-05-09' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
2 Joins in 1 Query
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.Booking_Type = Booking AND DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) = '2005-01-01' AND DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) = '2005-12-31' GROUP BY WEEK; The problem with this is that if I have no bookings for week 42 for example then that week is not shown in the result. To get round this I created a table called Weeks that contains all the week numbers for the year. However I am not sure how I can join Weeks to Bookings so that all the weeks show. Any healp would be greatly appreciated. TABLE DEF'S: mysql desc Bookings; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | Booking_ID | int(11) | | PRI | NULL| auto_increment | | Booking_Type| varchar(15) | | | Unavailability || | User_ID | int(11) | | | 0 || | Project_ID | int(11) | YES | | NULL|| | Rep_ID | int(11) | YES | | NULL|| | Practice_ID | int(11) | YES | | NULL|| | Booking_Creator_ID | int(11) | YES | | NULL|| | Booking_Creation_Date | datetime| YES | | NULL|| | Booking_Start_Date | datetime| | | -00-00 00:00:00 || | Booking_End_Date| datetime| | | -00-00 00:00:00 || | Booking_Completion_Date | date| YES | | NULL|| | Booking_Mileage | int(5) | YES | | NULL|| | Booking_Status | varchar(15) | | | Other || | Unavailability_ID | int(2) | YES | | NULL|| | Task_ID | int(11) | YES | | NULL|| | Work_Type_ID| int(2) | YES | | NULL|| | Additional_Notes| text| YES | | NULL|| +-+-+--+-+-++ 22 rows in set (0.00 sec) mysql desc Projects; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | Project_ID | int(11) | | PRI | NULL| auto_increment | | Project_Name | varchar(100) | | | | | | Client_ID | int(11) | | | 0 | | ++--+--+-+-++ 8 rows in set (0.00 sec) mysql desc Weeks; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | Week_ID | int(11) | | PRI | NULL| auto_increment | | Week_Number | int(11) | | | 0 || +-+-+--+-+-++ 2 rows in set (0.00 sec) mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 2 Joins in 1 Query
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],mysql@lists.mysql.com Subject: RE: 2 Joins in 1 Query Date: Mon, 23 May 2005 17:13:42 +0200 shaun thornburgh mailto:[EMAIL PROTECTED] wrote: 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.Booking_Type = Booking AND DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) = '2005-01-01' AND DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) = '2005-12-31' GROUP BY WEEK; The problem with this is that if I have no bookings for week 42 for example then that week is not shown in the result. To get round this I created a table called Weeks that contains all the week numbers for the year. Try a LEFT JOIN: 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 LEFT JOIN Projects P ON (P.Project_ID = B.Project_ID) WHERE B.Booking_Type = Booking AND DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) = '2005-01-01' AND DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) = '2005-12-31' GROUP BY WEEK; Regards Peter Normann -- 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]
Update Email Column
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 For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UPDATE Query
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 corresponds to your MySQL server version for the right syntax to use near 'SUBSTRING(PRACT_ASCII, 16, 6) = '11' WHERE Insertion_ID = 190716' at line 1 mysql Any advice would be greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Remove 1st 3 Chars
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]
ERROR 1111 (HY000): Invalid use of group function
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(SUBSTRING(PRACT_NUMBER, 1, 4)) 1 - AND Booking_ID = 6030 - GROUP BY PRACT_NUMBER; ERROR (HY000): Invalid use of group function mysql Can anyone tell me why this is happening? Thanks for your advice -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I find this data?
Hi Shawn, Thanks for your reply. The query you suggested seems to return all practices that have bookings where the day type does not equal '1' - I changed your query so it searches on the day_type rather than work_type as this is what is required: SELECT p.Practice_ID ,p.Practice_Name ,b.Booking_ID ,wt.Project_ID ,SUM(If(wt.Day_Type=1,1,0)) as WorkType_1 ,count(1) as TotalWorkType FROM Practices p INNER JOIN Bookings b ON b.Practice_ID = p.Practice_ID INNER JOIN Work_Types wt ON wt.Work_Type_ID = b.Work_Type_ID GROUP BY 1,2,3,4 HAVING WorkType_1=0; Let me give you an example. Your query returns this in the last row: +-+--++++---+ | Practice_ID | Practice_Name| Booking_ID | Project_ID | WorkType_1 | TotalWorkType | +-+--++++---+ | 11094 | Bugbrooke Surgery| 7114 | 12 | 0 | 1 | +-+--++++---+ But if i select all bookings where the practice_id = 11094 i get another booking where the day_type = 1: mysql SELECT * FROM Bookings WHERE Practice_ID = 11094; ++--+-+++-++---+-+-+-+-++---+-+--++---+-+---+-+---+ | Booking_ID | Booking_Type | User_ID | Project_ID | Rep_ID | Practice_ID | Booking_Creator_ID | Booking_Creation_Date | Booking_Start_Date | Booking_End_Date| Booking_Completion_Date | Booking_Mileage | Booking_Status | Unavailability_ID | Task_ID | Work_Type_ID | Additional_Notes | Pre_Event_Copy_Received_By_Scheduling | Post_Event_Original_Completed_Form_Received | Section_C | Date_Difference | AU_Booking_ID | ++--+-+++-++---+-+-+-+-++---+-+--++---+-+---+-+---+ | 7112 | Booking | 52 | 12 |638 | 11094 | 1174 | 2005-04-19 12:31:51 | 2005-05-04 09:30:00 | 2005-05-04 18:00:00 | NULL| 0 | Incomplete | NULL | 7 | 22 | x | No | No | No| n/a | NULL | | 7114 | Booking |1601 | 12 |638 | 11094 | 1174 | 2005-04-19 12:57:53 | 2005-05-25 09:30:00 | 2005-05-25 18:00:00 | NULL| 0 | Incomplete | NULL | 7 | 24 | x | No | No | No| n/a | NULL | ++--+-+++-++---+-+-+-+-++---+-+--++---+-+---+-+---+ 2 rows in set (0.02 sec) mysql SELECT * FROM Work_Types WHERE Project_ID = 12; +--++--+---+ | Work_Type_ID | Project_ID | Day_Type | Work_Type | +--++--+---+ | 22 | 12 |1 | Day 1 | | 24 | 12 |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 I find this data? Date: Wed, 4 May 2005 09:42:47 -0400 shaun thornburgh [EMAIL PROTECTED] wrote on 05/04/2005 06:54:23 AM: Hi, I have four tables among others in my database: Bookings, Work_Types, Practices Projects. Bookings occur
How can I find this data?
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 after the system was initially set up and I have a feeling there may be Practices that have no Day 1's. So how can I perform a query that returns all practices that have had bookings but no Day 1's for a particualar project? Please see table definitions below. Thanks very much for your help. mysql desc Bookings; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | Booking_ID | int(11) | | PRI | NULL| auto_increment | | Booking_Type| varchar(15) | | | Unavailability || | User_ID | int(11) | | | 0 || | Project_ID | int(11) | YES | | NULL|| | Rep_ID | int(11) | YES | | NULL|| | Practice_ID | int(11) | YES | | NULL|| | Booking_Creator_ID | int(11) | YES | | NULL|| | Booking_Creation_Date | datetime| YES | | NULL|| | Booking_Start_Date | datetime| | | -00-00 00:00:00 || | Booking_End_Date| datetime| | | -00-00 00:00:00 || | Booking_Completion_Date | date| YES | | NULL|| | Booking_Mileage | int(5) | YES | | NULL|| | Booking_Status | varchar(15) | | | Other || | Unavailability_ID | int(2) | YES | | NULL|| | Task_ID | int(11) | YES | | NULL|| | Work_Type_ID| int(2) | YES | | NULL|| | Additional_Notes| text| YES | | NULL|| | Pre_Event_Copy_Received_By_Scheduling | char(3) | YES | | NULL|| | Post_Event_Original_Completed_Form_Received | char(3) | YES | | NULL|| | Section_C | char(3) | YES | | NULL|| | Date_Difference | varchar(20) | | | n/a || | AU_Booking_ID | int(11) | YES | | NULL|| +-+-+--+-+-++ 22 rows in set (0.00 sec) mysql desc Projects; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | Project_ID | int(11) | | PRI | NULL| auto_increment | | Project_Name | varchar(100) | | | | | | Client_ID | int(11) | | | 0 | | | Rep_Viewable | char(3) | | | Yes | | | Administrator_ID | int(11) | YES | | NULL| | | Administrator_Phone_Number | varchar(20) | | | | | | Project_Manager_ID_1 | int(11) | YES | | NULL| | | Project_Manager_ID_2 | int(11) | YES | | NULL| | ++--+--+-+-++ 8 rows in set (0.00 sec) mysql desc Practices; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | Practice_ID|
RE: How can I find this data?
Hi Jay, Thanks for your reply, I tried your query but it jus hang :( Here is some sample data: mysql SELECT * FROM Bookings WHERE Practice_ID = 11049; ++--+-+++-++---+-+-+-+-++---+-+--+--+---+-+---+-+---+ | Booking_ID | Booking_Type | User_ID | Project_ID | Rep_ID | Practice_ID | Booking_Creator_ID | Booking_Creation_Date | Booking_Start_Date | Booking_End_Date| Booking_Completion_Date | Booking_Mileage | Booking_Status | Unavailability_ID | Task_ID | Work_Type_ID | Additional_Notes | Pre_Event_Copy_Received_By_Scheduling | Post_Event_Original_Completed_Form_Received | Section_C | Date_Difference | AU_Booking_ID | ++--+-+++-++---+-+-+-+-++---+-+--+--+---+-+---+-+---+ | 6148 | Booking |1571 | 32 | 1629 | 11049 | 75 | 2005-03-11 13:29:40 | 2005-04-01 09:30:00 | 2005-04-01 17:30:00 | NULL| 0 | Incomplete | NULL | 26 | 76 | x| Yes | NULL | No| n/a | NULL | | 6149 | Booking |1571 | 32 | 1629 | 11049 | 75 | 2005-03-11 13:30:49 | 2005-05-06 09:30:00 | 2005-05-06 17:30:00 | NULL|NULL | Incomplete | NULL | 26 | 77 | x| 0 | NULL | 0 | n/a | NULL | | 50 | Booking |1571 | 3 | 1629 | 11049 | 75 | 2005-03-11 13:31:20 | 2005-05-09 09:30:00 | 2005-05-09 17:30:00 | NULL|NULL | Incomplete | NULL | 26 |9 | x| 0 | NULL | 0 | n/a | NULL | | 55 | Booking |1645 | 3 | 1629 | 11049 | 75 | 2005-04-01 11:38:24 | 2005-04-04 09:30:00 | 2005-04-04 17:30:00 | NULL| 0 | Incomplete | NULL | 26 |9 | x| No | NULL | No| n/a | NULL | ++--+-+++-++---+-+-+-+-++---+-+--+--+---+-+---+-+---+ 4 rows in set (0.02 sec) mysql SELECT * FROM Work_Types WHERE Project_ID = 32; +--++--++ | Work_Type_ID | Project_ID | Day_Type | Work_Type | +--++--++ | 76 | 32 |1 | Day 1 | | 77 | 32 |2 | Day 2 | +--++--++ 2 rows in set (0.00 sec) mysql SELECT * FROM Work_Types WHERE Project_ID = 3; +--++--++ | Work_Type_ID | Project_ID | Day_Type | Work_Type | +--++--++ |8 | 3 |1 | Day 1 | |9 | 3 |2 | Day 2 | +--++--++ 2 rows in set (0.00 sec) mysql 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: shaun thornburgh [EMAIL PROTECTED], mysql@lists.mysql.com Subject: RE: How can I find this data? Date: Wed, 4 May 2005 06:36:18 -0500 [snip] I have four tables among others in my database: Bookings, Work_Types, Practices Projects. Bookings occur
Help with a DATETIME query please!
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' has a start date and time and an end date and time. Dates for bookings don't have to be the same, i.e. a user could be on holiday for a week. The follwing query produces an empty result: SELECT (8.5 - TIMEDIFF(B.Booking_End_Date, B.Booking_Start_Date)) AS 'Availability' FROM Bookings B WHERE B.User_ID = '1' AND NOT ( CURDATE() DATE_FORMAT(Booking_Start_Date, %Y-%m-%d) CURDATE() DATE_FORMAT(Booking_End_Date, %Y-%m-%d) ) GROUP BY Availability ORDER BY B.Booking_Start_Date; Here is the Definition for the bookings table: +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | Booking_ID | int(11) | | PRI | NULL| auto_increment | | Booking_Type| varchar(15) | | | Unavailability || | User_ID | int(11) | | | 0 || | Project_ID | int(11) | YES | | NULL|| | Booking_Creator_ID | int(11) | YES | | NULL|| | Booking_Creation_Date | datetime| YES | | NULL|| | Booking_Start_Date | datetime| | | -00-00 00:00:00 || | Booking_End_Date| datetime| | | -00-00 00:00:00 || | Booking_Status | varchar(15) | | | Other || | Additional_Notes| text| YES | | NULL|| +-+-+--+-+-++ Thanks for any advice offered here -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
HELP WITH A DATE QUERY USING 4.0.17
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. Users work 8.5 hours a day and the query shows how many hours available the user has on that day: SELECT 8.5 - (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'))) / 60) AS Available_Hours FROM Bookings B WHERE B.User_ID = '610' AND NOT ( '2005-04-08' DATE_FORMAT(Booking_Start_Date, %Y-%m-%d) OR '2005-04-08' DATE_FORMAT(Booking_End_Date, %Y-%m-%d) ) The problem here is I have to do this query to produce a result for each cell(date) and then for each user so 100 users = 1000 queries to load the page! Is there a way to produce the result so that I only need one query per user so it groups the result by day for the next ten days? Thanks for your help Here is the table definition: mysql desc Bookings; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | Booking_ID | int(11) | | PRI | NULL| auto_increment | | Booking_Type| varchar(15) | | | Unavailability || | User_ID | int(11) | | | 0 || | Project_ID | int(11) | YES | | NULL|| | Booking_Creator_ID | int(11) | YES | | NULL|| | Booking_Creation_Date | datetime| YES | | NULL|| | Booking_Start_Date | datetime| | | -00-00 00:00:00 || | Booking_End_Date| datetime| | | -00-00 00:00:00 || | Booking_Completion_Date | date| YES | | NULL|| | Booking_Mileage | int(5) | YES | | NULL|| | Booking_Status | varchar(15) | | | Other || | Additional_Notes| text| YES | | NULL|| +-+-+--+-+-++ 23 rows in set (0.00 sec) mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
TIME Difference Using 4.0.17
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 For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with a JOIN query please
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, SUM(M.Mileage) AS Mileage FROM Claims C LEFT JOIN Expenses E ON E.Claim_ID = C.Claim_ID LEFT JOIN Mileage M ON M.Claim_ID = C.Claim_ID WHERE C.Claimant_ID = '1' GROUP BY C.Claim_ID The problem is if there are two mileage entries and one expense entry the expense total is doubled (and vice versa), can some explain this to me please? Thanks for your help. TABLE DEFINITIONS: mysql desc Claims; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | Claim_ID| int(11) | | PRI | NULL| auto_increment | | Claimant_ID | int(11) | | | 0 || | Description | varchar(50) | | | || | Status | varchar(50) | YES | | Open|| | Submission_Date | datetime| YES | | NULL|| | Approval_Date | datetime| YES | | NULL|| | Approver_ID | int(11) | YES | | NULL|| +-+-+--+-+-++ 7 rows in set (0.00 sec) mysql desc Expenses; +-+---+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-++ | Expense_ID | int(11) | | PRI | NULL| auto_increment | | Claim_ID| int(11) | YES | | NULL| | | Description | varchar(50) | YES | | NULL| | | Expense_Category_ID | int(11) | YES | | NULL| | | Insertion_Date | date | YES | | NULL| | | Project_ID | int(11) | YES | | NULL| | | Amount | decimal(10,2) | YES | | NULL| | | Rate_ID | int(11) | YES | | NULL| | | Supplier_ID | int(11) | YES | | NULL| | | Receipt | varchar(10) | YES | | NULL| | | Receipt_Date| varchar(10) | YES | | NULL| | | VAT_Receipt | varchar(10) | YES | | NULL| | | VAT_Amount | decimal(10,2) | YES | | NULL| | +-+---+--+-+-++ 13 rows in set (0.00 sec) mysql desc Mileage; ++--+--+-+++ | Field | Type | Null | Key | Default| Extra | ++--+--+-+++ | Mileage_ID | int(11) | | PRI | NULL | auto_increment | | Claim_ID | int(11) | | | 0 || | Project_ID | int(11) | | | 0 || | Insertion_Date | date | | | -00-00 || | Description| varchar(255) | | ||| | Start_Mileage | int(11) | | | 0 || | End_Mileage| int(11) | | | 0 || | Mileage| int(11) | | | 0 || ++--+--+-+++ 8 rows in set (0.00 sec) mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Column Order
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]
MySQL CRON
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 have to upload the files and not wait for the insert? Any advice / examples here would be great as I am not a UNIX expert! Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA INFILE XML
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]
Help with a query please
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 BY User_Firstname; The query is meant to return all the users in the allocations table plus user 101, however the query returns 15 instances of the 101 user along with all the users in the allocations table... Any help here would be greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a query please
From: Rhino [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: Help with a query please Date: Mon, 21 Feb 2005 08:22:29 -0500 - Original Message - From: shaun thornburgh [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, February 21, 2005 7:28 AM Subject: Help with a query please 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 BY User_Firstname; The query is meant to return all the users in the allocations table plus user 101, however the query returns 15 instances of the 101 user along with all the users in the allocations table... What's the problem then? It seems to be doing exactly what you want to do. Can you clarify how the actual result differs from the expected result? So far, they sound the same Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.1.0 - Release Date: 18/02/2005 Hi Rhino, Thanks for your reply, I would like the query to retun one instance of user 101 rather than 15! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA INFILE Opposite
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 list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA INFILE using 4.0.17
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 files. Is it possible to use the LOAD DATA INFILE statement to check for unique rows, or does anyone have a better solution to my problem! Thanks for any advice offered. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE using 4.0.17
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 using 4.0.17 Date: Tue, 15 Feb 2005 23:32:56 +0100 shaun thornburgh ha scritto: 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 files. Is it possible to use the LOAD DATA INFILE statement to check for unique rows, or does anyone have a better solution to my problem! Thanks for any advice offered. make a table of 27 fields ;) , use the MD5() function http://dev.mysql.com/doc/mysql/en/encryption-functions.html , simply add a unique index on the column that holds the md5 sum. Regards, Francesco -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE using 4.0.17
No just every row needs to be unique. Sorry for the confusion... From: Robert Dunlop [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: LOAD DATA INFILE using 4.0.17 Date: Tue, 15 Feb 2005 15:06:19 -0800 So what you meant was every field in each row must be unique from all other instances in all other rows? Not just each row must be unique? Bob - Original Message - From: shaun thornburgh [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, February 15, 2005 2:40 PM Subject: Re: LOAD DATA INFILE using 4.0.17 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 using 4.0.17 Date: Tue, 15 Feb 2005 23:32:56 +0100 shaun thornburgh ha scritto: 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 files. Is it possible to use the LOAD DATA INFILE statement to check for unique rows, or does anyone have a better solution to my problem! Thanks for any advice offered. make a table of 27 fields ;) , use the MD5() function http://dev.mysql.com/doc/mysql/en/encryption-functions.html , simply add a unique index on the column that holds the md5 sum. Regards, Francesco -- 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Add an ENUM Column
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]
ENCODE DECODE
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 select all the data and decrypt any fields as required. Is this possible with one query, or do I have to select every data item then decrypt it then present the data? Thanks for your help -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ENCODE DECODE
Apparently not! mysql SELECT DECODE ( CSV_DATA_ENC_FORENAME, test_password ) FROM DATA_TABLE_PID_1_DESC_137; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '( CSV_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 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 select all the data and decrypt any fields as required. Is this possible with one query, or do I have to select every data item then decrypt it then present the data? Wouldn't select decode(col1) from table work? Jeff attach3 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SHOW TABLES Problem
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 unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW TABLES Problem
Thanks for your reply, I have a query where I select all table names where the table name has PID_1 in i.e. SHOW TABLES LIKE '%PID_1%'; However there may be cases where I need to search for tables where the table name is PID_1 or PID_2. Do you another way I might be able to do this? From: Gabriel PREDA [EMAIL PROTECTED] Reply-To: Gabriel PREDA [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: SHOW TABLES Problem Date: Wed, 2 Feb 2005 13:48:43 +0200 Because it does not know REGEXP... only LIKE patterns... % Matches any number of characters, even zero characters _ Matches exactly one character Taake a look in chapter 13 String Comparison Functions. Gabriel PREDA - Original Message - From: shaun thornburgh [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, February 02, 2005 1:03 PM Subject: SHOW TABLES Problem 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 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]
Undo function?
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 for your help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SHOW COLUMNS Syntax Using 3.23.54 Please!
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 archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can Mysql hold possible field values?
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 application this way would ensure that no invalid data can be entered into the 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]
Re: Can Mysql hold possible field values?
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.mysql.com Subject: Re: Can Mysql hold possible field values? Date: Mon, 17 Jan 2005 15:23:42 +0100 At 2005-01-17 15:12, shaun thornburgh wrote: 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. http://dev.mysql.com/doc/mysql/en/ENUM.html -- 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]
SHOW COLUMNS Syntax Using 3.23.54
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 archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with an UPDATE query please
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 bookings so that the Task_ID is the same as the Task_ID in the Tasks table for that project and the task is a booking? Thanks for your help mysql DESCRIBE Bookings; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | Booking_ID | int(11) | | PRI | NULL| auto_increment | | Booking_Type| varchar(15) | | | Unavailability | | | User_ID | int(11) | | | 0 | | | Project_ID | int(11) | YES | | NULL| | | Rep_ID | int(11) | YES | | NULL| | | PCT_ID | int(11) | YES | | NULL| | | Practice_ID | int(11) | YES | | NULL| | | Booking_Creator_ID | int(11) | YES | | NULL| | | Booking_Creation_Date | datetime| YES | | NULL| | | Booking_Start_Date | datetime| | | -00-00 00:00:00 | | | Booking_End_Date| datetime| | | -00-00 00:00:00 | | | Booking_Completion_Date | date| YES | | NULL| | | Booking_Mileage | int(5) | YES | | NULL| | | Booking_Status | varchar(15) | | | Other | | | Unavailability_ID | int(2) | YES | | NULL| | | Task_ID | int(11) | YES | | NULL| | | Work_Type_ID| int(2) | YES | | NULL| | | Additional_Notes| text| YES | | NULL| | | Form_Recieved | char(3) | | | | | | Section_C | char(3) | | | | | | Date_Difference | varchar(20) | | | n/a | | +-+-+--+-+-++ 21 rows in set (0.01 sec) mysql DESCRIBE Projects; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | Project_ID | int(11) | | PRI | NULL| auto_increment | | Project_Name | varchar(100) | | | || | Client_ID| int(11) | | | 0 || | Rep_Viewable | char(3) | | | Yes || | User_ID | int(11) | YES | | NULL|| +--+--+--+-+-++ 5 rows in set (0.00 sec) mysql DESCRIBE Tasks; ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | Task_ID| int(11) | | PRI | NULL| auto_increment | | Task_Name | varchar(40) | | | || | Project_ID | int(11) | | | 0 || ++-+--+-+-++ 3 rows in set (0.00 sec) mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a join query please!
Hi Shawn, Thanks for your reply, but your query seems to return everyone allocated to that project plus everyone who has ever been booked on that project. What I need is everyone who is currently allocated to that project and the staff member who has been booked for the booking in question whether they have been allocated to the project or not. In reply to your quesion bookings don't relate to allocations directly. A booking relates to a project by Project_ID and to a user by User_ID. Allocations contains both of these fields to determine who is 'Allocated' to a particular 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 booking to an allocation? Do they share a project_ID or what? If they do, you might try this: SELECT DISTINCT U.User_ID, U.User_Firstname, U.User_Lastname FROM Users U LEFT JOIN Allocations A on A.User_ID = U.User_ID LEFT JOIN Bookings B ON B.User_ID = U.User_ID WHERE A.Project_ID = '11' OR B.Project_ID = '11' ORDER BY User_Firstname; soapbox A pet peeve of mine is when people 'quote' NUMBERS. According to the extremely well written manual, you only need to quote STRING values and DATETIME values. Unless the columns Project_ID and Booking_ID are some form of STRING column (CHAR, VARCHAR, TEXT, etc.) you don't need to quote their values in queries. It forces the query engine to perform an unnecessary internal type conversion. Here is what I think your query should look like: SELECT DISTINCT U.User_ID, U.User_Firstname, U.User_Lastname FROM Users U LEFT JOIN Allocations A on A.User_ID = U.User_ID LEFT JOIN Bookings B ON B.User_ID = U.User_ID WHERE A.Project_ID = 11 OR B.Project_ID = 11 ORDER BY User_Firstname; /soapbox I used SELECT DISTINCT so that in the event that someone was both BOOKED and ALLOCATED to the same project, you only got them listed once. Shawn Green Database Administrator Unimin Corporation - Spruce Pine shaun thornburgh [EMAIL PROTECTED] wrote on 12/23/2004 08:37:37 AM: 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 thornburgh wrote: 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 allocated to that project plus the person originally booked. Here are my efforts so far: SELECT U.User_ID, U.User_Firstname, U.User_Lastname FROM Allocations A, Users U LEFT JOIN Bookings B ON B.User_ID = U.User_ID AND B.Booking_ID = '4512' WHERE U.User_ID = A.User_ID AND A.Project_ID = '11' ORDER BY User_Firstname; Shaun: If I understand the problem right, it sounds like you are missing AND B.Bookings_ID is NULL in the where clause. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with an UPDATE query please
Sorry Jay, I am using version 3.23.54, unfortunately I am unable to supply test data but basically I need to update the Task_ID column for the Bookings table so that it is the same as the Task_ID for Tasks and the Task_Name is 'Booking' and the Bookings Project_ID is the same as 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 [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED], mysql@lists.mysql.com Subject: RE: Help with an UPDATE query please Date: Wed, 29 Dec 2004 13:53:07 -0600 [snip] 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 bookings so that the Task_ID is the same as the Task_ID in the Tasks table for that project and the task is a booking? [/snip] Can you show us some test data and an example of what you want to happen? Also, what version of MySQL are you running? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a join query please!
Hi Shawn, Sorry for the confusions! The booking in question is where B.Booking_ID = x as in the original query: SELECT U.User_ID, U.User_Firstname, U.User_Lastname FROM Allocations A, Users U LEFT JOIN Bookings B ON B.User_ID = U.User_ID AND B.Booking_ID = 4512 WHERE U.User_ID = A.User_ID AND 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 [EMAIL PROTECTED] wrote on 12/29/2004 03:00:08 PM: Hi Shawn, Thanks for your reply, but your query seems to return everyone allocated to that project plus everyone who has ever been booked on that project. What I need is everyone who is currently allocated to that project and the staff member who has been booked for the booking in question whether they have been allocated to the project or not. How can I determine the booking in question? What confluence of values makes a generic booking the booking in question. If you could walk me through the process of finding it manually (compare field x on table y to field z on table bb then go over to table c and look at ...) I can get you the data you need. In reply to your quesion bookings don't relate to allocations directly. A booking relates to a project by Project_ID and to a user by User_ID. Allocations contains both of these fields to determine who is 'Allocated' to a particular project. Thanks again for your help! any time :-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine 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 booking to an allocation? Do they share a project_ID or what? If they do, you might try this: SELECT DISTINCT U.User_ID, U.User_Firstname, U.User_Lastname FROM Users U LEFT JOIN Allocations A on A.User_ID = U.User_ID LEFT JOIN Bookings B ON B.User_ID = U.User_ID WHERE A.Project_ID = '11' OR B.Project_ID = '11' ORDER BY User_Firstname; soapbox A pet peeve of mine is when people 'quote' NUMBERS. According to the extremely well written manual, you only need to quote STRING values and DATETIME values. Unless the columns Project_ID and Booking_ID are some form of STRING column (CHAR, VARCHAR, TEXT, etc.) you don't need to quote their values in queries. It forces the query engine to perform an unnecessary internal type conversion. Here is what I think your query should look like: SELECT DISTINCT U.User_ID, U.User_Firstname, U.User_Lastname FROM Users U LEFT JOIN Allocations A on A.User_ID = U.User_ID LEFT JOIN Bookings B ON B.User_ID = U.User_ID WHERE A.Project_ID = 11 OR B.Project_ID = 11 ORDER BY User_Firstname; /soapbox I used SELECT DISTINCT so that in the event that someone was both BOOKED and ALLOCATED to the same project, you only got them listed once. Shawn Green Database Administrator Unimin Corporation - Spruce Pine shaun thornburgh [EMAIL PROTECTED] wrote on 12/23/2004 08:37:37 AM: 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 thornburgh wrote: 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 allocated to that project plus the person originally booked. Here are my efforts so far: SELECT U.User_ID, U.User_Firstname, U.User_Lastname FROM Allocations A, Users U LEFT JOIN Bookings B ON B.User_ID = U.User_ID AND B.Booking_ID = '4512' WHERE U.User_ID = A.User_ID AND A.Project_ID = '11' ORDER BY User_Firstname; Shaun: If I understand the problem right, it sounds like you are missing AND B.Bookings_ID is NULL in the where clause. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A Complicated SELECT query
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))) / 60 ) AS Booked_Hours FROM Bookings B, Projects P WHERE B.User_ID = 610 AND B.Booking_Type = Booking AND P.Project_ID = 2 AND B.Project_ID = P.Project_ID AND NOT ( 2005-01-10 DATE_FORMAT(Booking_Start_Date, %Y-%m-%d) OR 2005-01-10 DATE_FORMAT(Booking_End_Date, %Y-%m-%d) ) Bookings are related to table Projects by Project_ID and to table Users by User_ID. I need to update this query to cater for another type of booking. The Booking_Type = Task, and the booking is related to Table Tasks by Task_ID. Each Project will have many Tasks and this is how the booking indirectly relates to a project. Therefore my question is how can i check how many hours a user has been booked for on a particular date relating to a particular project whatever the type of the booking is? If I am checking for a 'Booking' I need to check that B.Project_ID = P.Project_ID and if I am checking for a 'Task' I need to check that AND B.Task_ID = T.Task_ID AND T.Project_ID = P.Project_ID. Thanks for your help Shaun Table Definitions: mysql DESCRIBE Bookings; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | Booking_ID | int(11) | | PRI | NULL| auto_increment | | Booking_Type| varchar(15) | | | Unavailability | | | User_ID | int(11) | | | 0 | | | Project_ID | int(11) | YES | | NULL| | | Rep_ID | int(11) | YES | | NULL| | | PCT_ID | int(11) | YES | | NULL| | | Practice_ID | int(11) | YES | | NULL| | | Booking_Creator_ID | int(11) | YES | | NULL| | | Booking_Creation_Date | datetime| YES | | NULL| | | Booking_Start_Date | datetime| | | -00-00 00:00:00 | | | Booking_End_Date| datetime| | | -00-00 00:00:00 | | | Booking_Completion_Date | date| YES | | NULL| | | Booking_Mileage | int(5) | YES | | NULL| | | Booking_Status | varchar(15) | | | Other | | | Unavailability_ID | int(2) | YES | | NULL| | | Task_ID | int(11) | YES | | NULL| | | Work_Type_ID| int(2) | YES | | NULL| | | Additional_Notes| text| YES | | NULL| | | Form_Recieved | char(3) | | | | | | Section_C | char(3) | | | | | | Date_Difference | varchar(20) | | | n/a | | +-+-+--+-+-++ 21 rows in set (0.00 sec) mysql DESCRIBE Users; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | User_ID | int(11) | | PRI | NULL| auto_increment | | Client_ID| int(3) | YES | | NULL| | | User_Username| varchar(40) | | | | | | User_Firstname | varchar(50) | YES | | NULL| | | User_Lastname| varchar(50) | YES | | NULL| | | User_Password| varchar(20) | YES | | NULL| | | User_Type| varchar(20) | | | Nurse | | | User_Email | varchar(100) | YES | | NULL| | | User_Manager_Email | varchar(100) | YES | | NULL| | | User_Manager_Email_2 | varchar(100) | YES | | NULL| | | User_Manager_Email_3 | varchar(100) | YES | | NULL| | | User_Manager_Email_4 | varchar(100) | YES | | NULL| | |
Re: Help with a join query please!
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 thornburgh wrote: 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 allocated to that project plus the person originally booked. Here are my efforts so far: SELECT U.User_ID, U.User_Firstname, U.User_Lastname FROM Allocations A, Users U LEFT JOIN Bookings B ON B.User_ID = U.User_ID AND B.Booking_ID = '4512' WHERE U.User_ID = A.User_ID AND A.Project_ID = '11' ORDER BY User_Firstname; Shaun: If I understand the problem right, it sounds like you are missing AND B.Bookings_ID is NULL in the where clause. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- 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]
Help with a join query please!
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 allocated to that project plus the person originally booked. Here are my efforts so far: SELECT U.User_ID, U.User_Firstname, U.User_Lastname FROM Allocations A, Users U LEFT JOIN Bookings B ON B.User_ID = U.User_ID AND B.Booking_ID = '4512' WHERE U.User_ID = A.User_ID AND A.Project_ID = '11' ORDER BY User_Firstname; I am using version 3.23.54. The query works but doesnt return the required values. The query returns all of the users allocated to the project, not the user who is currently booked but not allocated to the project. Here are the table definitions: mysql DESCRIBE Allocations; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | Project_ID | int(11) | | PRI | 0 | | | User_ID| int(11) | | PRI | 0 | | ++-+--+-+-+---+ 2 rows in set (0.00 sec) mysql DESCRIBE Bookings; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | Booking_ID | int(11) | | PRI | NULL| auto_increment | | Booking_Type| varchar(15) | | | Unavailability | | | User_ID | int(11) | | | 0 | | | Project_ID | int(11) | YES | | NULL| | | Rep_ID | int(11) | YES | | NULL| | | PCT_ID | int(11) | YES | | NULL| | | Practice_ID | int(11) | YES | | NULL| | | Booking_Creator_ID | int(11) | YES | | NULL| | | Booking_Creation_Date | datetime| YES | | NULL| | | Booking_Start_Date | datetime| | | -00-00 00:00:00 | | | Booking_End_Date| datetime| | | -00-00 00:00:00 | | | Booking_Completion_Date | date| YES | | NULL| | | Booking_Mileage | int(5) | YES | | NULL| | | Booking_Status | varchar(15) | | | Other | | | Unavailability_ID | int(2) | YES | | NULL| | | Work_Type_ID| int(2) | YES | | NULL| | | Additional_Notes| text| YES | | NULL| | | Form_Recieved | char(3) | | | | | | Section_C | char(3) | | | | | | Date_Difference | varchar(20) | | | n/a | | +-+-+--+-+-++ 20 rows in set (0.00 sec) mysql DESCRIBE Projects; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | Project_ID | int(11) | | PRI | NULL| auto_increment | | Project_Name | varchar(100) | | | || | Client_ID| int(11) | | | 0 || | Rep_Viewable | char(3) | | | Yes || | User_ID | int(11) | YES | | NULL|| +--+--+--+-+-++ 5 rows in set (0.00 sec) mysql DESCRIBE Users; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | User_ID | int(11) | | PRI | NULL| auto_increment | | Client_ID| int(3) | YES | | NULL| | | User_Username| varchar(40) | | | | | | User_Firstname | varchar(50) | YES | | NULL| | | User_Lastname| varchar(50) | YES | | NULL| | | User_Password| varchar(20) | YES |
Help with a join query please
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 allocated to that project plus the person originally booked. Here are my efforts so far: SELECT U.User_ID, U.User_Firstname, U.User_Lastname FROM Allocations A, Users U LEFT JOIN Bookings B ON B.User_ID = U.User_ID AND B.Booking_ID = '4512' WHERE U.User_ID = A.User_ID AND A.Project_ID = '11' ORDER BY User_Firstname; I hope this makes sense! Shaun -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a join query please
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 PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 16, 2004 2:02 PM Subject: Help with a join query please 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 allocated to that project plus the person originally booked. Here are my efforts so far: SELECT U.User_ID, U.User_Firstname, U.User_Lastname FROM Allocations A, Users U LEFT JOIN Bookings B ON B.User_ID = U.User_ID AND B.Booking_ID = '4512' WHERE U.User_ID = A.User_ID AND A.Project_ID = '11' ORDER BY User_Firstname; I hope this makes sense! We could do a lot more for you if you mentioned a few things: a) which version of MySQL you are using b) whether the query works or not the way it stands c) if it does work, what is wrong with the answer you are getting d) if it doesn't work, what error message you are getting And if you REALLY want to help us help you, you could provide definitions of the tables and a few rows of typical data from each table to help us visualize the problem better. Rhino Sorry guys! a) I am using version 3.23.54 b) The query works but doesnt return the required values c) The query returns all of the users allocated to the project, not the user who is currently booked but not allocated to the project d) No error message! Here are the table definitions: mysql DESCRIBE Allocations; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | Project_ID | int(11) | | PRI | 0 | | | User_ID| int(11) | | PRI | 0 | | ++-+--+-+-+---+ 2 rows in set (0.00 sec) mysql DESCRIBE Bookings; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | Booking_ID | int(11) | | PRI | NULL| auto_increment | | Booking_Type| varchar(15) | | | Unavailability | | | User_ID | int(11) | | | 0 | | | Project_ID | int(11) | YES | | NULL| | | Rep_ID | int(11) | YES | | NULL| | | PCT_ID | int(11) | YES | | NULL| | | Practice_ID | int(11) | YES | | NULL| | | Booking_Creator_ID | int(11) | YES | | NULL| | | Booking_Creation_Date | datetime| YES | | NULL| | | Booking_Start_Date | datetime| | | -00-00 00:00:00 | | | Booking_End_Date| datetime| | | -00-00 00:00:00 | | | Booking_Completion_Date | date| YES | | NULL| | | Booking_Mileage | int(5) | YES | | NULL| | | Booking_Status | varchar(15) | | | Other | | | Unavailability_ID | int(2) | YES | | NULL| | | Work_Type_ID| int(2) | YES | | NULL| | | Additional_Notes| text| YES | | NULL| | | Form_Recieved | char(3) | | | | | | Section_C | char(3) | | | | | | Date_Difference | varchar(20) | | | n/a | | +-+-+--+-+-++ 20 rows in set (0.00 sec) mysql DESCRIBE Projects; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | Project_ID | int(11) | | PRI | NULL| auto_increment | | Project_Name | varchar(100) | | | || | Client_ID| int(11) | | | 0 || | Rep_Viewable | char(3) | | | Yes
Help with a DATETIME Query Please Using 3.23.54
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))) / 60 ) AS Hours_Booked FROM Bookings B WHERE B.User_ID = 1 AND NOT ( 2004-11-01 DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) OR 2004-11-01 DATE_FORMAT(B.Booking_End_Date, %Y-%m-%d) ) ; However it fails if the booking spans more than one day. For example I have a booking that starts at 9am on 2004-10-31 and ends at 10am on 2004-11-02, and for this the query returns one hour, when it should be 24! Any pointers regarding this would be most apprecited Shaun -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
List of Dates Grouped by Week
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 | Type| Null | Key | Default| Extra | +---+-+--+-+++ | Timesheet_ID | int(11) | | PRI | NULL | auto_increment | | Type | varchar(40) | YES | | NULL | | | Project_ID| int(11) | YES | | NULL | | | User_ID | int(11) | | | 0 | | | Hours | float | | | 0 | | | Date | date| | | -00-00 | | | Status| varchar(40) | YES | | Open | | +---+-+--+-+++ When a timesheet is submitted for approval the manager logs in and approves / rejects the timesheets. How can I display a list of unapproved timesheets grouped by week and user? i.e. Week 1 - Bill - 45 Hours Week 1 - Fred - 40 Hours Week 2 - Bill - 45 Hours Week 2 - Fred - 40 Hours Week 2 - Sam - 12 Hours Thanks for your help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A Complicated DATETIME query using 3.23.54
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 | +---+-+--+-+++ | Booking_ID| int(11) | | PRI | NULL | auto_increment | | User_ID | int(11) | | | 0 | | | Booking_Start_Date| datetime| YES | | NULL | | | Booking_End_Date | datetime| YES | | NULL | | +---+-+--+-+++ I use the following query to extract how many hours have been worked ona 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))) / 60 ) AS Available_Hours FROM Bookings B WHERE B.User_ID = 1 AND NOT ( 2004-10-25 DATE_FORMAT(Booking_Start_Date, %Y-%m-%d) OR 2004-10-25 DATE_FORMAT(Booking_End_Date, %Y-%m-%d) ) Is it possible to group this information by day for the next seven days? Thanks for your help -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECTING Non existing Dates
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 = Project; ++---+--+ | Date | Hours | Project_Name | ++---+--+ | 2004-10-18 | 4 | Project A| | 2004-10-19 | 3 | Project A| | 2004-10-20 | 4 | Project A| | 2004-10-21 | 1 | Project A| | 2004-10-22 | 2 | Project A| | 2004-10-18 | 3 | Project B| | 2004-10-19 | 5 | Project B| | 2004-10-20 | 3 | Project B| | 2004-10-21 | 7 | Project B| | 2004-10-22 | 6 | Project B| ++---+--+ 10 rows in set (0.00 sec) mysql Any ideas? From: Martijn Tonies [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: SELECTING Non existing Dates Date: Wed, 20 Oct 2004 15:10:51 +0200 Hello Shaun, 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 T.Date = 2004-10-24 AND T.User_ID = 1 AND T.Type = Project GROUP BY T.Date, T.Project_ID ORDER BY T.Date Try: 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 = Project And here is the result: ++---+--+ | Date | Hours | Project_Name | ++---+--+ | 2004-10-18 | 4 | Project A| | 2004-10-18 | 3 | Project B| | 2004-10-19 | 3 | Project A| | 2004-10-19 | 5 | Project B| | 2004-10-20 | 4 | Project A| | 2004-10-20 | 3 | Project B| | 2004-10-21 | 1 | Project A| | 2004-10-21 | 7 | Project B| | 2004-10-22 | 2 | Project A| | 2004-10-22 | 6 | Project B| ++---+--+ With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- 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]
SELECTING Non existing Dates
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 T.Date = 2004-10-24 AND T.User_ID = 1 AND T.Type = Project GROUP BY T.Date, T.Project_ID ORDER BY T.Date And here is the result: ++---+--+ | Date | Hours | Project_Name | ++---+--+ | 2004-10-18 | 4 | Project A| | 2004-10-18 | 3 | Project B| | 2004-10-19 | 3 | Project A| | 2004-10-19 | 5 | Project B| | 2004-10-20 | 4 | Project A| | 2004-10-20 | 3 | Project B| | 2004-10-21 | 1 | Project A| | 2004-10-21 | 7 | Project B| | 2004-10-22 | 2 | Project A| | 2004-10-22 | 6 | Project B| ++---+--+ All is working fine except I would like the query to show zero values for days where there are no entries i.e. the 23rd and 24th in this example. I thought that the LEFT JOIN would achieve this but unfortunately not! If someone could show me where I am going wrong I would be most grateful. Many thanks, Shaun -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
COUNT Problem
Hi, I have the following two tables in my database: mysql DESCRIBE Projects; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | Project_ID | int(11) | | PRI | NULL| auto_increment | | Client_ID| int(11) | YES | | NULL|| | Project_Name | varchar(100) | YES | | NULL|| +--+--+--+-+-++ 3 rows in set (0.00 sec) mysql DESCRIBE Tasks; ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | Task_ID| int(11) | | PRI | NULL| auto_increment | | Task_Name | varchar(40) | | | || | Project_ID | int(11) | | | 0 || ++-+--+-+-++ 3 rows in set (0.00 sec) mysql A Project will have 1 or more tasks assigned to it. Using the following query, how can I modify it so that I can find out the largest number of tasks assigned to a group of projects. SELECT P.*, T.* FROM Projects P, Tasks T WHERE P.Project_ID = T.Project_ID AND Project_ID 2; So if Project A has 3 tasks and Project B has 4 tasks I need the query to return 4. Hope this makes sense! Thanks for your help -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Joing two fields in a query
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 _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Updating Names Column
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 help _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with a join query
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 allocated to? This query is fine if the user is allocated to one or more projects: SELECT U.*, P.* FROM Users U, Allocations A, Projects P WHERE A.Project_ID = P.Project_ID AND A.User_ID = U.User_ID AND U.User_ID = '2'; And if i put a join in the query i get hundreds of results: SELECT U.*, P.* FROM Users U, Allocations A LEFT JOIN Projects P ON P.Project_ID = A.Project_ID AND A.User_ID = U.User_ID WHERE U.User_ID = '2'; Any help here would be great, many thanks. mysql DESCRIBE Users; +--+---+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-++ | User_ID | int(11) | | PRI | NULL| auto_increment | | User_Username| varchar(40) | | | | | | User_Password| varchar(20) | YES | | NULL| | | User_Name| varchar(100) | | | | | | User_Type| varchar(20) | | | Nurse | | | User_Email | varchar(100) | YES | | NULL| | +--+---+--+-+-++ 17 rows in set (0.01 sec) mysql DESCRIBE Allocations; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | Project_ID | int(11) | | PRI | 0 | | | User_ID| int(11) | | PRI | 0 | | ++-+--+-+-+---+ 2 rows in set (0.00 sec) mysql DESCRIBE Projects; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | Project_ID | int(11) | | PRI | NULL| auto_increment | | Project_Name | varchar(100) | | | || +--+--+--+-+-++ 5 rows in set (0.01 sec) _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with a SELECT Query
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.System_ID Is there a way to allow the query to extract the practuce information even if the system hasnt been set? Thanks for your help. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with a Date Query Please!
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. Users work 8.5 hours a day and the query shows how many hours available the user has on that day: SELECT 8.5 - (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'))) / 60) AS Available_Hours FROM Bookings B WHERE B.User_ID = '610' AND NOT ( '2004-07-08' DATE_FORMAT(Booking_Start_Date, %Y-%m-%d) OR '2004-07-08' DATE_FORMAT(Booking_End_Date, %Y-%m-%d) ) The problem here is I have to do this query to produce a result for each cell and then for each user so 10 users = 100 queries to load the page! Is there a way to produce the result so that I only need one query per user so it groups the result by day for the next ten days? Thanks for your help _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with a DATETIME Query
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. Users work 8.5 hours a day and the query shows how many hours available the user has on that day: SELECT 8.5 - (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'))) / 60) AS Available_Hours FROM Bookings B WHERE B.User_ID = '610' AND NOT ( '2004-07-08' DATE_FORMAT(Booking_Start_Date, %Y-%m-%d) OR '2004-07-08' DATE_FORMAT(Booking_End_Date, %Y-%m-%d) ) The problem here is I have to do this query to produce a result for each cell and then for each user so 10 users = 100 queries to load the page! Is there a way to produce the result so that I only need one query per user so it groups the result by day for the next ten days? Thanks for your help _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT SELECT
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, Project_ID) SELECT Work_Types.Work_Type_ID FROM Work_Types WHERE Work_Types.Project_ID = 'x'; 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 archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Group Query
Thanks for your reply, This works great, but when I add a constraint such as WHERE B.Project_ID = '10' I dont get the NULL values from the User table which I need: SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U LEFT OUTER JOIN Bookings B ON U.User_ID = B.Rep_ID WHERE B.Project_ID = '8' GROUP BY(U.User_Location); Any ideas, do I need to do a double join, or do I need to join the projects table also? Thanks for your help From: Paul McNeil [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: RE: Group Query Date: Fri, 18 Jun 2004 08:20:10 -0400 You can use a Left Outer Join. Left Outer Join will include all that matches as well as that which doesn't. The resulting NULL entries for the count will evaluate to 0. SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U LEFT OUTER JOIN Bookings B ON U.User_ID = B.User_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 Query 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 bookings are made by a particular user then their location isnt included in the result. Is it possible to modify this query so that if no bookings are made for a particlar location then the location is still included in the result i.e. +-+---+ | COUNT(B.Booking_ID) | User_Location | +-+---+ | 1 | 01| | 8 | 02 | | 9 | 03 | | 1 | 04 | | 1 | 05 | | 2 | 06 | | 1 | 07 | ... Many thanks for your help _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- 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] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Group Query
Hi, Thanks for your reply Johan, this works however I actually onley wanted the rows where Project_ID = '10'. SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U LEFT OUTER JOIN Bookings B ON U.User_ID = B.Rep_ID AND B.Project_ID = '10' GROUP BY(U.User_Location); This 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 [EMAIL PROTECTED] CC: [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: Group Query Date: Tue, 22 Jun 2004 10:56:12 +0200 Shaun, when you add WHERE B.Project_ID = '10' you, in a way, change your LEFT JOIN to an INNER JOIN. You need to do it like: LEFT OUTER JOIN Bookings B ON U.User_ID = B.Rep_ID AND B.Project_ID = '8' /Johan shaun thornburgh wrote: Thanks for your reply, This works great, but when I add a constraint such as WHERE B.Project_ID = '10' I dont get the NULL values from the User table which I need: SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U LEFT OUTER JOIN Bookings B ON U.User_ID = B.Rep_ID WHERE B.Project_ID = '8' GROUP BY(U.User_Location); Any ideas, do I need to do a double join, or do I need to join the projects table also? Thanks for your help From: Paul McNeil [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: RE: Group Query Date: Fri, 18 Jun 2004 08:20:10 -0400 You can use a Left Outer Join. Left Outer Join will include all that matches as well as that which doesn't. The resulting NULL entries for the count will evaluate to 0. SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U LEFT OUTER JOIN Bookings B ON U.User_ID = B.User_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 Query 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 bookings are made by a particular user then their location isnt included in the result. Is it possible to modify this query so that if no bookings are made for a particlar location then the location is still included in the result i.e. +-+---+ | COUNT(B.Booking_ID) | User_Location | +-+---+ | 1 | 01| | 8 | 02 | | 9 | 03 | | 1 | 04 | | 1 | 05 | | 2 | 06 | | 1 | 07 | ... Many thanks for your help _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- 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] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- Johan Höök, Pythagoras Engineering Group - MailTo:[EMAIL PROTECTED] - http://www.pythagoras.se Pythagoras AB, Stormbyv. 2-4, SE-163 55 SPÅNGA, Sweden Phone: +46 8 760 00 10 Fax: +46 8 761 22 77 _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A Complicated Group Query
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 have taken place for that location. SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U LEFT OUTER JOIN Bookings B ON U.User_ID = B.User_ID GROUP BY(U.User_Location); This is useful as it shows zero values where no bookings have taken place. However, Each booking is related to a particular project by Project_ID. How can I modify this query such that it only returns a count of bookings where the Project_ID = 'x'? If I modify this query to: SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U LEFT OUTER JOIN Bookings B ON U.User_ID = B.User_ID AND B.Project_ID = '10' GROUP BY(U.User_Location); It returns all User Locations in the User table, regardless of the Project_ID of the Booking. And if I modify the query to: SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U LEFT OUTER JOIN Bookings B ON U.User_ID = B.User_ID WHERE B.Project_ID = '10' GROUP BY(U.User_Location); I lose the zero/NULL Values... Any comments here would be greatly apprecited! _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A Complicated Group Query
Hi Shawn, Thanks for your reply, I am still having problems here though! The first query produces this: +-+---+ | COUNT(B.Booking_ID) | User_Location | +-+---+ |1818 | NULL | | 1 | 204 | | 1 | 301 | | 1 | 302 | | 1 | 408 | +-+---+ The location counts here are correct except for the NULL, but the other locations havent been included. The second query, produces a complete list of all the Users locations! There is another table that might help here; Clients. Each User is referenced to a client and each project is referenced to a client. So a client has many projects and many users in the database. If I modify my initial query to: SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U LEFT OUTER JOIN Bookings B ON U.User_ID = B.User_ID WHERE U.Client_ID = 'X' GROUP BY(U.User_Location); It works perefectly for all clients that have one project. But I need to be able to produce the same report for individual projects rather than clients! 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 FROM Users U LEFT OUTER JOIN Bookings B ON U.User_ID = B.User_ID AND B.Project_ID = '10' GROUP BY(U.User_Location); It returns all User Locations in the User table, regardless of the Project_ID of the Booking. That's exactly how the LEFT JOIN is supposed to work. The users are on the LEFT side of the LEFT JOIN so you will get _all_ rows from that table and only those rows from the table on the *other* side of the join that satisfy your ON conditions. You can flip your query two ways. Change either LEFT JOIN to RIGHT JOIN or exchange the positions of the table names around the JOIN keywords SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U RIGHT OUTER JOIN Bookings B ON U.User_ID = B.User_ID AND B.Project_ID = '10' GROUP BY(U.User_Location); -OR- SELECT COUNT(B.Booking_ID), U.User_Location FROM Bookings B RIGHT OUTER JOIN Users U ON U.User_ID = B.User_ID AND B.Project_ID = '10' GROUP BY(U.User_Location); Either one of these statements puts the Bookings table into the controlling side of the partial join. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine shaun thornburgh [EMAIL PROTECTED]To: [EMAIL PROTECTED] otmail.com cc: Fax to: 06/22/2004 09:41 Subject: A Complicated Group Query AM 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 have taken place for that location. SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U LEFT OUTER JOIN Bookings B ON U.User_ID = B.User_ID GROUP BY(U.User_Location); This is useful as it shows zero values where no bookings have taken place. However, Each booking is related to a particular project by Project_ID. How can I modify this query such that it only returns a count of bookings where the Project_ID = 'x'? If I modify this query to: SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U LEFT OUTER JOIN Bookings B ON U.User_ID = B.User_ID AND B.Project_ID = '10' GROUP BY(U.User_Location); It returns all User Locations in the User table, regardless of the Project_ID of the Booking. And if I modify the query to: SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U LEFT OUTER JOIN Bookings B ON U.User_ID = B.User_ID WHERE B.Project_ID = '10' GROUP BY(U.User_Location); I lose the zero/NULL Values... Any comments here would be greatly apprecited! _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- 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] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A Complicated Group Query
Hi Shawn,
A slight correction(!) Client_ID is contained in the Projects table as a
Client can have many projects. Therefore c.Project_ID will cause an error...
Here is a definition of the tables:
mysql DESCRIBE Users;
+--+--+--+-+-++
| Field| Type | Null | Key | Default | Extra
|
+--+--+--+-+-++
| User_ID | int(11) | | PRI | NULL|
auto_increment |
| Client_ID| int(3) | YES | | NULL|
|
| User_Username| varchar(40) | | | |
|
| User_Password| varchar(20) | YES | | NULL|
|
| User_Name| varchar(100) | | | |
|
| User_Type| varchar(20) | | | Nurse |
|
| User_Email | varchar(100) | YES | | NULL|
|
| User_Location| varchar(40) | YES | | NULL|
|
+--+--+--+-+-++
15 rows in set (0.00 sec)
mysql DESCRIBE Projects;
+--+--+--+-+-++
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-++
| Project_ID | int(11) | | PRI | NULL| auto_increment |
| Project_Name | varchar(100) | | | ||
| Client_ID| int(11) | | | 0 ||
+--+--+--+-+-++
4 rows in set (0.00 sec)
mysql DESCRIBE Clients;
+-+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+-+--+--+-+-++
| Client_ID | int(11) | | PRI | NULL| auto_increment |
| Client_Name | varchar(100) | YES | | NULL||
+-+--+--+-+-++
2 rows in set (0.00 sec)
mysql DESCRIBE Bookings;
+-+-+--+-+-++
| Field | Type| Null | Key | Default |
Extra |
+-+-+--+-+-++
| Booking_ID | int(11) | | PRI | NULL|
auto_increment |
| Booking_Type| varchar(15) | | | Unavailability |
|
| User_ID | int(11) | | | 0 |
|
| Project_ID | int(11) | YES | | NULL|
|
| Booking_Creator_ID | int(11) | YES | | NULL|
|
| Booking_Creation_Date | datetime| YES | | NULL|
|
| Booking_Start_Date | datetime| | | -00-00 00:00:00 |
|
| Booking_End_Date| datetime| | | -00-00 00:00:00 |
|
| Booking_Completion_Date | date| YES | | NULL|
|
| Booking_Mileage | int(5) | YES | | NULL|
|
| Booking_Status | varchar(15) | | | Other |
|
| Additional_Notes| text| YES | | NULL|
|
+-+-+--+-+-++
20 rows in set (0.01 sec)
mysql
Thanks a million for your help here :)
From: [EMAIL 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 errors like that) 8-{
If I understand you correctly, you want to see a count of how many bookings
you have had per project broken down by user location. Try this:
SELECT c.Project_ID, u.User_Location, COUNT(b.booking_ID) as Bookings
FROM Clients c
LEFT JOIN Users u
on u.Client_ID = c.Client_ID
LEFT JOIN Bookings b
on b.User_ID = u.User_ID
GROUP BY c.Project_ID, u.User_Location
Or for just one project, we can do this:
SELECT c.Project_ID, u.User_Location, COUNT(b.booking_ID) as Bookings
FROM Clients c
LEFT JOIN Users u
on u.Client_ID = c.Client_ID
and c.Project_ID = 'x'
LEFT JOIN Bookings b
on b.User_ID = u.User_ID
GROUP BY c.Project_ID, u.User_Location
--- or this
SELECT c.Project_ID, u.User_Location, COUNT(b.booking_ID) as Bookings
FROM Clients c
LEFT JOIN Users u
on u.Client_ID = c.Client_ID
LEFT
Group Query
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 bookings are made by a particular user then their location isnt included in the result. Is it possible to modify this query so that if no bookings are made for a particlar location then the location is still included in the result i.e. +-+---+ | COUNT(B.Booking_ID) | User_Location | +-+---+ | 1 | 01| | 8 | 02 | | 9 | 03 | | 1 | 04 | | 1 | 05 | | 2 | 06 | | 1 | 07 | ... Many thanks for your help _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query on Projects Information Grouped by Month
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 May Jun Jul Project 1 6 7 3 0 3 43 Project 2 5 4 5 7 1251 Project 3 8 7 1 9 214 1 Thanks for your help _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: A query that groups information by month
Hi, Thanks for your reply Peter. Each booking is based on a particular project by Project_ID. Is it possible to modify the query such that the projects are listed in turn? For example: Jan Feb Mar Apr May Jun Jul Project 1 6 7 3 0 3 4 3 Project 2 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 month Date: Thu, 10 Jun 2004 11:18:38 +0100 select month(booking_date) bookings_month, count(bookings) as bookings GROUP BY month(booking_date) Peter -Original Message- From: shaun thornburgh [mailto:[EMAIL PROTECTED] Sent: 10 June 2004 11:12 To: [EMAIL PROTECTED] Subject: A query that groups information by month 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 _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Email has been scanned for viruses and SPAM by Trader Mailmanager www.trader.uk.com _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DELETE from more than one table
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:50:39 -0400 The manual says ON DELETE CASCADE with foreign keys is available for InnoDB tables starting with mysql 3.23.50 http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html. Michael shaun thornburgh wrote: Hi Robert, Thanks for the reply but unfortunately I am still using version 3.23.54 and I am unable to change it! From: Robert A. Rosenberg [EMAIL PROTECTED] At 14:01 + 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 this (you can then insert it if you did not want to delete it but only the records that pointed at it). I do not know how helpful this is. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DELETE from more than one table
Hi Robert, Thanks for the reply but unfortunately I am still using version 3.23.54 and I am unable to change it! From: Robert A. Rosenberg [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: RE: DELETE from more than one table Date: Mon, 7 Jun 2004 18:04:38 -0400 At 14:01 + 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 this (you can then insert it if you did not want to delete it but only the records that pointed at it). I do not know how helpful this is. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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? From: Paul McNeil [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED] Subject: RE: DELETE from more than one table Date: Mon, 7 Jun 2004 08:47:04 -0400 I believe you can use... Delete from TableA join TableB ON TableA.data = TableB.data WHERE TableA.otherData = myNeeds. GOD BLESS AMERICA! To God Be The Glory! -Original Message- From: shaun thornburgh [mailto:[EMAIL PROTECTED] Sent: Monday, June 07, 2004 8:37 AM To: [EMAIL PROTECTED] Subject: DELETE from more than one table 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 archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DELETE from more than one table
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 archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with a Select Query
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 returns the
two nurses details based on the User_ID of the Rep:
SELECT U1.*, U2.*
FROM Users U1, Users U2, Users R
WHERE R.User_ID = 'x'
AND U1.User_ID = R.Rep_Nurse_1
AND U2.User_ID = R.Rep_Nurse_2
However this returns just the first nurse allocated to that rep.
I have listed my User table defination below and would be most grateful for
anyones advice here.
html
head
titlequery data/title
style type=text/css
!--
.normal { font-family: Verdana, Arial, Helvetica, sans-serif; font-size:
12px; font-weight: normal; color: #00}
.medium { font-family: Verdana, Arial, Helvetica, sans-serif; font-size:
15px; font-weight: bold; color: #00; text-decoration: none}
--/style
/head
h3query result ( 15 records )/h3table border=1
tr
td bgcolor=silver class='medium'Field/tdtd bgcolor=silver
class='medium'Type/tdtd bgcolor=silver class='medium'Null/tdtd
bgcolor=silver class='medium'Key/tdtd bgcolor=silver
class='medium'Default/tdtd bgcolor=silver class='medium'Extra/td/tr
tr
td class='normal' valign='top'User_ID/tdtd class='normal'
valign='top'int(11)/tdtd class='normal' valign='top'nbsp;/tdtd
class='normal' valign='top'PRI/tdtd class='normal'
valign='top'(NULL)/tdtd class='normal'
valign='top'auto_increment/td/trtr
td class='normal' valign='top'Client_ID/tdtd class='normal'
valign='top'int(3)/tdtd class='normal' valign='top'YES/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'(NULL)/tdtd class='normal' valign='top'nbsp;/td/trtr
td class='normal' valign='top'User_Username/tdtd class='normal'
valign='top'varchar(40)/tdtd class='normal' valign='top'nbsp;/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'nbsp;/tdtd class='normal' valign='top'nbsp;/td/trtr
td class='normal' valign='top'User_Password/tdtd class='normal'
valign='top'varchar(20)/tdtd class='normal' valign='top'YES/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'(NULL)/tdtd class='normal' valign='top'nbsp;/td/trtr
td class='normal' valign='top'User_Name/tdtd class='normal'
valign='top'varchar(100)/tdtd class='normal' valign='top'nbsp;/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'nbsp;/tdtd class='normal' valign='top'nbsp;/td/trtr
td class='normal' valign='top'User_Type/tdtd class='normal'
valign='top'varchar(20)/tdtd class='normal' valign='top'nbsp;/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'Nurse/tdtd class='normal' valign='top'nbsp;/td/trtr
td class='normal' valign='top'User_Email/tdtd class='normal'
valign='top'varchar(100)/tdtd class='normal' valign='top'YES/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'(NULL)/tdtd class='normal' valign='top'nbsp;/td/trtr
td class='normal' valign='top'User_Manager_Email/tdtd class='normal'
valign='top'varchar(100)/tdtd class='normal' valign='top'YES/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'(NULL)/tdtd class='normal' valign='top'nbsp;/td/trtr
td class='normal' valign='top'User_Manager_Email_2/tdtd class='normal'
valign='top'varchar(100)/tdtd class='normal' valign='top'YES/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'(NULL)/tdtd class='normal' valign='top'nbsp;/td/trtr
td class='normal' valign='top'User_Manager_Email_3/tdtd class='normal'
valign='top'varchar(100)/tdtd class='normal' valign='top'YES/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'(NULL)/tdtd class='normal' valign='top'nbsp;/td/trtr
td class='normal' valign='top'User_Location/tdtd class='normal'
valign='top'varchar(40)/tdtd class='normal' valign='top'YES/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'(NULL)/tdtd class='normal' valign='top'nbsp;/td/trtr
td class='normal' valign='top'User_Administrator/tdtd class='normal'
valign='top'int(2)/tdtd class='normal' valign='top'YES/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'(NULL)/tdtd class='normal' valign='top'nbsp;/td/trtr
td class='normal' valign='top'Mobile_Number/tdtd class='normal'
valign='top'varchar(20)/tdtd class='normal' valign='top'YES/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'(NULL)/tdtd class='normal' valign='top'nbsp;/td/trtr
td class='normal' valign='top'Rep_Nurse_1/tdtd class='normal'
valign='top'int(11)/tdtd class='normal' valign='top'YES/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'(NULL)/tdtd class='normal' valign='top'nbsp;/td/trtr
td class='normal' valign='top'Rep_Nurse_2/tdtd class='normal'
valign='top'int(11)/tdtd class='normal' valign='top'YES/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'(NULL)/tdtd
MySQL Users Table
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 documentation on the mysql site? THanks for your help _ Sign-up for a FREE BT Broadband connection today! http://www.msn.co.uk/specials/btbroadband -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
lower_case_table_names
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\binmysqld --SET-lower_case_table_names=2; Ideally I would like this to be permanet so I have also investagated editing the my.cnf file: snip 4.1.2 `my.cnf' Option Files MySQL can, since Version 3.22, read default startup options for the server and for clients from option files. On Windows, MySQL reads default options from the following files: Filename Purpose windows-directory\my.ini Global options C:\my.cnf Global options windows-directory is the location of your Windows directory. /snip But neither of these files exist on my system. I would be most grateful if someone could show me how to run the command line option or edit the necessary file to make these changes permanent. Thanks for your help _ Express yourself with cool emoticons - download MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Case Sensitive on Windows
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 errors because the MySQL installation on the local machine isn't case sensitive, therefore it isn't recognizing the same tables. How can I rectify this, I can't find any documentation on the MySQL site regarding this...? Thanks for your help _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL on Win2k Server open to connections from all IPs
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 to connect to it. I did consider adding individual IP addresses but some people are allocated new IP addresses everytime they connect to the internet - i.e. dial up accounts... Thanks for your help _ Sign-up for a FREE BT Broadband connection today! http://www.msn.co.uk/specials/btbroadband -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with a DATETIME Query PLEASE!
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 have unbooked on a particular day (i.e. 8.5 hours less the time of any bookings on that day). However, when a booking spans more than one day the query doesn't work, for example if a user has a booking that starts on day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5 hours for both days. Any help here would be greatly appreciated. SELECT 8.5 - (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'))) / 60) AS Available_Hours FROM WMS_Bookings B WHERE B.User_ID = '16' AND B.Booking_Status '1' AND NOT ( '2003-10-07' DATE_FORMAT(Booking_Start_Date, %Y-%m-%d) OR '2003-10-07' DATE_FORMAT(Booking_End_Date, %Y-%m-%d) ) Thanks for your help _ On the move? Get Hotmail on your mobile phone http://www.msn.co.uk/msnmobile -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help With a DATETIME Query PLEASE!
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 have unbooked on a particular day (i.e. 8.5 hours less the time of any bookings on that day). However, when a booking spans more than one day the query doesn't work, for example if a user has a booking that starts on day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5 hours for both days. Any help here would be greatly appreciated. SELECT 8.5 - (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'))) / 60) AS Available_Hours FROM WMS_Bookings B WHERE B.User_ID = '16' AND B.Booking_Status '1' AND NOT ( '2003-10-07' DATE_FORMAT(Booking_Start_Date, %Y-%m-%d) OR '2003-10-07' DATE_FORMAT(Booking_End_Date, %Y-%m-%d) ) Thanks for your help _ Find a cheaper internet access deal - choose one to suit you. http://www.msn.co.uk/internetaccess -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help With a DATETIME Query
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 have unbooked on a particular day (i.e. 8.5 hours less the time of any bookings on that day). However, when a booking spans more than one day the query doesn't work, for example if a user has a booking that starts on day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5 hours for both days. The query is run for each day i.e day 1, day 2 day 10. Any help here would be greatly appreciated. SELECT 8.5 - (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'))) / 60) AS Available_Hours FROM WMS_Bookings B WHERE B.User_ID = '16' AND B.Booking_Status '1' AND NOT ( '2003-10-07' DATE_FORMAT(Booking_Start_Date, %Y-%m-%d) OR '2003-10-07' DATE_FORMAT(Booking_End_Date, %Y-%m-%d) ) _ On the move? Get Hotmail on your mobile phone http://www.msn.co.uk/msnmobile -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help With a DATETIME Query
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 answer you're looking for... :( but dealing with overnights has caused me so much aggravation in past apps I've written, I've tended to make the client create two (or more) 'bookings' for the covered time... don't know if it's an option for you, but it's my $0.02. -Original Message- From: shaun thornburgh [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 4:33 PM To: [EMAIL PROTECTED] Subject: Help With a DATETIME Query 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 have unbooked on a particular day (i.e. 8.5 hours less the time of any bookings on that day). However, when a booking spans more than one day the query doesn't work, for example if a user has a booking that starts on day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5 hours for both days. The query is run for each day i.e day 1, day 2 day 10. Any help here would be greatly appreciated. SELECT 8.5 - (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'))) / 60) AS Available_Hours FROM WMS_Bookings B WHERE B.User_ID = '16' AND B.Booking_Status '1' AND NOT ( '2003-10-07' DATE_FORMAT(Booking_Start_Date, %Y-%m-%d) OR '2003-10-07' DATE_FORMAT(Booking_End_Date, %Y-%m-%d) ) _ On the move? Get Hotmail on your mobile phone http://www.msn.co.uk/msnmobile -- 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] _ Use MSN Messenger to send music and pics to your friends http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help With a DATETIME Query
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 have unbooked on a particular day (i.e. 8.5 hours less the time of any bookings on that day). However, when a booking spans more than one day the query doesn't work, for example if a user has a booking that starts on day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5 hours for both days. Any help here would be greatly appreciated. SELECT 8.5 - (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'))) / 60) AS Available_Hours FROM WMS_Bookings B WHERE B.User_ID = '16' AND B.Booking_Status '1' AND NOT ( '2003-10-07' DATE_FORMAT(Booking_Start_Date, %Y-%m-%d) OR '2003-10-07' DATE_FORMAT(Booking_End_Date, %Y-%m-%d) ) Thanks for your help _ Get Hotmail on your mobile phone http://www.msn.co.uk/msnmobile -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help With a DATETIME Query
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 have unbooked on a particular day (i.e. 8.5 hours less the time of any bookings on that day). However, when a booking spans more than one day the query doesn't work, for example if a user has a booking that starts on day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5 hours for both days. Any help here would be greatly appreciated. SELECT 8.5 - (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'))) / 60) AS Available_Hours FROM WMS_Bookings B WHERE B.User_ID = '16' AND B.Booking_Status '1' AND NOT ( '2003-10-07' DATE_FORMAT(Booking_Start_Date, %Y-%m-%d) OR '2003-10-07' DATE_FORMAT(Booking_End_Date, %Y-%m-%d) ) Thanks for your help _ On the move? Get Hotmail on your mobile phone http://www.msn.co.uk/msnmobile -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
