timw

Yep worked a treat had to do a bit of tidying first as i had a few April's not in as Apr and Sept's not in as Sep UPDATE table SET var_field = REPLACE(table.var_field,'April','Apr') WHERE table.var_field LIKE '%April%'; Then ran UPDATE table SET date_field = STR_TO_DATE(var_field, '%b %e, %Y') WHERE id = id; Thanks, Tim

Hi, My current mysql db has a varchar field with a date stored in the following format 'Aug 12, 2011' or 'Sept 9, 2012' etc. Do any of you know of a good way I could select the dates and insert in to a new date field in the correct YYYY-mm-dd format? Many thanks, Tim

Thanks for your responses guys, Keith, after a quick look yours was returning 0 rows on my larger database so not quite sure why... i need to read through and see where yours is going there. mikosiko, Thats great runs a lot quicker on the larger database and also gives me a bonus: The agents that have not selected any operators at all now show too, I cant quite work out why that is yet but will have a bit more of a read in a little while. Sorry for being thick what do you mean by "Adding an INDEX on the column user_id in the table selected_operators should help too." Thanks again, Tim

Hi guys I'm looking for a little help with this query I've been looking at it a little too long and would like a second set of eyes! I have attached the code to create the tables and insert the data with my code to return the result as I would expect. The issue is that my database is getting quite a few entries now BUT not as many as i hope to have in it and already the query is slow. I'm sure there is a better way of writing the query and any comments on my code would be appreciated! my OBJECT is to get a list AGENTS (id, cname) that have not yet selected Operator_2. just so you notice AGENTS are in the login_user table with the user_level of 3. My query would return the result that Agent_4 (user_id =8 ) is the only Agent that has not yet selected Operator_2. /* login_users Table user_id user_level cname 1 1 Admin 2 2 Operator_1 3 2 Operator_2 4 2 Operator_3 5 3 Agent_1 6 3 Agent_2 7 3 Agent_3 8 3 Agent_4 selected_operators Table id user_id operator_id 1 5 2 // Agent_1 HAS SELECTED Operator_1 2 5 3 // Agent_1 HAS SELECTED Operator_2 3 5 4 // Agent_1 HAS SELECTED Operator_3 4 6 2 // Agent_2 HAS SELECTED Operator_1 5 6 3 // Agent_2 HAS SELECTED Operator_2 6 7 3 // Agent_3 HAS SELECTED Operator_2 7 7 4 // Agent_3 HAS SELECTED Operator_3 8 8 2 // Agent_4 HAS SELECTED Operator_1 9 8 4 // Agent_4 HAS SELECTED Operator_3 */ CREATE TABLE `login_users` ( `user_id` INT(11) NOT NULL AUTO_INCREMENT, `user_level` INT(1) NOT NULL DEFAULT '3', `cname` VARCHAR(255) DEFAULT NULL, PRIMARY KEY (`user_id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `selected_operators` ( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `user_id` INT(11) NOT NULL, `operator_id` INT(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; INSERT INTO `login_users` (`user_id`, `user_level`, `cname`) VALUES (1, 1, 'ADMIN'), (2, 2, 'Operator_1'), (3, 2, 'Operator_2'), (4, 2, 'Operator_3'), (5, 3, 'Agent_1'), (6, 3, 'Agent_2'), (7, 3, 'Agent_3'), (8, 3, 'Agent_4'); INSERT INTO `selected_operators` (`id`, `user_id`, `operator_id`) VALUES (1, 5, 2), (2, 5, 3), (3, 5, 4), (4, 6, 2), (5, 6, 3), (6, 7, 3), (7, 7, 4), (8, 8, 2), (9, 8, 4); SELECT DISTINCT(so.user_id), lu.cname FROM selected_operators AS so INNER JOIN login_users AS lu ON so.user_id = lu.user_id WHERE so.user_id NOT IN (SELECT DISTINCT user_id FROM selected_operators WHERE operator_id = 3) AND lu.user_level = 3 ORDER BY so.user_id; Thanks, Tim oh and if it has any influence i'm currently using MySQL 5.5.9