Well, the only thing that matters is that you are returning the same number of columns with, presumably, the same data types in both queries. Obviously, UNION wouldn't be helpful if you had 2 columns in the first query and 8 columns in the second.
I'll leave it to you to find the documentation on UNION - it's not that difficult :-). Basically it's something like SELECT A, B, C FROM FOO UNION SELECT X, Y, Z FROM FOO Good Luck! Dennis ----- Original Message ----- From: "electroteque" <[EMAIL PROTECTED]> To: "MyLists" <[EMAIL PROTECTED]>; "MySQL List" <[EMAIL PROTECTED]>; "otherguy" <[EMAIL PROTECTED]> Cc: "Terry Vanstory" <[EMAIL PROTECTED]> Sent: Saturday, June 28, 2003 12:05 AM Subject: RE: Advanced Query Help (My brain hurts!) > 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]
