I need help with two things:
1) How can I effectively combine the following two queries? The only difference between them is the `appl` condition. I effectively need to do an INNER JOIN on the zip code of the results of both queries so that only zip codes that exist in both result sets are returned:
SELECT quota_zip2.zipcode, SUM(quota_control2.quota_actual) AS sum_actual, SUM(quota_control2.quota_limit) AS sum_limit FROM quota_zip2 INNER JOIN quota_control2 ON (quota_zip2.agent_code = quota_control2.agent_code) AND (quota_zip2.appl = quota_control2.appl) WHERE quota_control2.appl = "CIRG" GROUP BY quota_zip2.zipcode HAVING sum_actual >= sum_limit ORDER BY quota_zip2.zipcode;
SELECT quota_zip2.zipcode, SUM(quota_control2.quota_actual) AS sum_actual, SUM(quota_control2.quota_limit) AS sum_limit FROM quota_zip2 INNER JOIN quota_control2 ON (quota_zip2.agent_code = quota_control2.agent_code) AND (quota_zip2.appl = quota_control2.appl) WHERE quota_control2.appl = "CILT" GROUP BY quota_zip2.zipcode HAVING sum_actual >= sum_limit ORDER BY quota_zip2.zipcode;
I cannot change the where clause to
WHERE `appl` = "CIRG" OR `appl` = "CILT"
because there might be data that would result in a situation where the the sum_actual would meet or exceed the sum_limit for a zip code (using both `appl`'s in the where), whereas running them separately would result in the sum_actual not being met for one of the `appl`'s for that zip_code (it would have been exceeded for the other `appl`).
2) Once I have this query, how can I then update a third table based on it? I basically need to run:
UPDATE listmaster SET status = "WD" WHERE zipcode = <any zipcode in results of the query from above>.
I think that this would involve another inner join, but at this point I'm _WAY_ over my head.
I've included a dump of sample tables and data at the end of this e-mail.
For the record:
1) I know that this whole thing would be easier to do programatically (it would take a while, but even I could do it that way). Unfortunately due to complications of the environment over which I have absolutely no control, that's not an option.
2) This database is not of my design.
3) This will eventually go into a nightly maintenance job, so query execution time is not a big issue.
Finally, in advance, I really appreciate any time and effort any of you are willing to put in. Hopefully there's someone out there that enjoys figuring stuff like this out who has more expertise than I do. Any assistance rendered will result in many thanks from me.
Thanks again, -Cameron Wilhelm
-=-=-=-=-=-=-=BEGIN DUMP OF SAMPLE DATA=-=-=-=-=-=-=-=-=-
# Tables dumped 2003-06-27 19:08:11 -0600 # Created by CocoaMySQL (Copyright (c) 2002-2003 Lorenz Textor) # # Host: localhost Database: nbl_test # ******************************
# Dump of table listmaster # ------------------------------
CREATE TABLE `listmaster` ( `id` int(11) NOT NULL auto_increment, `zipcode` char(5) default NULL, `status` char(2) default NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`) ) TYPE=MyISAM;
INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES ("1","11111","NC");
INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES ("2","11111","NC");
INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES ("3","11111","NC");
INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES ("4","11111","NC");
INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES ("5","22222","NC");
INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES ("6","22222","NC");
INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES ("7","22222","NC");
INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES ("8","22222","NC");
INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES ("9","33333","NC");
INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES ("10","33333","NC");
INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES ("11","33333","NC");
INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES ("12","33333","NC");
# Dump of table quota_control2 # ------------------------------
CREATE TABLE `quota_control2` ( `agent_code` char(6) default NULL, `appl` char(4) default NULL, `quota_limit` smallint(6) default NULL, `quota_actual` smallint(6) default NULL, UNIQUE KEY `agent_code` (`agent_code`,`appl`) ) TYPE=MyISAM;
INSERT INTO `quota_control2` (`agent_code`,`appl`,`quota_limit`,`quota_actual`) VALUES ("a1","CIRG","10","10");
INSERT INTO `quota_control2` (`agent_code`,`appl`,`quota_limit`,`quota_actual`) VALUES ("a2","CIRG","5","6");
INSERT INTO `quota_control2` (`agent_code`,`appl`,`quota_limit`,`quota_actual`) VALUES ("a2","CILT","5","0");
INSERT INTO `quota_control2` (`agent_code`,`appl`,`quota_limit`,`quota_actual`) VALUES ("a3","CILT","5","0");
# Dump of table quota_zip2 # ------------------------------
CREATE TABLE `quota_zip2` ( `agent_code` char(6) default NULL, `appl` char(4) default NULL, `zipcode` char(5) default NULL, UNIQUE KEY `agent_code` (`agent_code`,`appl`,`zipcode`) ) TYPE=MyISAM;
INSERT INTO `quota_zip2` (`agent_code`,`appl`,`zipcode`) VALUES ("a1","CIRG","11111");
INSERT INTO `quota_zip2` (`agent_code`,`appl`,`zipcode`) VALUES ("a1","CIRG","22222");
INSERT INTO `quota_zip2` (`agent_code`,`appl`,`zipcode`) VALUES ("a2","CILT","22222");
INSERT INTO `quota_zip2` (`agent_code`,`appl`,`zipcode`) VALUES ("a2","CIRG","22222");
INSERT INTO `quota_zip2` (`agent_code`,`appl`,`zipcode`) VALUES ("a3","CILT","11111");
INSERT INTO `quota_zip2` (`agent_code`,`appl`,`zipcode`) VALUES ("a3","CILT","22222");
INSERT INTO `quota_zip2` (`agent_code`,`appl`,`zipcode`) VALUES ("a3","CILT","33333");
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
