That gets me halfway there, and not to my surprise, it's not even that hard! I should've known that it wouldn't be.
So the other part that I truly have no idea how to do the following:
UPDATE this_other_table SET satus = .... WHERE zipcode IN (<result set from union query blow>);
can someone provide me with some pointers in the right direction?
(SELECT
quota_zip2.zipcode
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(quota_control2.quota_actual) >= SUM(quota_control2.quota_limit) )
UNION
(SELECT
quota_zip2.zipcode
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(quota_control2.quota_actual) >= SUM(quota_control2.quota_limit) )
ORDER BY zipcode;
Thanks! -Cameron Wilhelm
On Friday, June 27, 2003, at 06:42 PM, MyLists wrote:
How about a UNION statement? If the two queries are independently returning
what you need, then you can just "append" the two results by using UNION.
Good Luck!
Dennis
----- Original Message ----- From: "otherguy" <[EMAIL PROTECTED]> To: "MySQL List" <[EMAIL PROTECTED]> Cc: "Terry Vanstory" <[EMAIL PROTECTED]> Sent: Friday, June 27, 2003 8:50 PM Subject: Advanced Query Help (My brain hurts!)
Hey guys, I'm about to dump a doozy on your collective knowledge and
goodwill, and hope for some help or some pointers. I'm not great with
advanced SQL, and I've gotten as far as my brain and the resources I've
been using will allow me to get for the time being.
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]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
