would u have an example of how to use union then ? like on the second select i need to reutn a heap of rows from a field from the first select
-----Original Message----- From: MyLists [mailto:[EMAIL PROTECTED] Sent: Saturday, June 28, 2003 10:42 AM To: MySQL List; otherguy Cc: Terry Vanstory Subject: Re: Advanced Query Help (My brain hurts!) 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
