Re: Advanced Query Help (My brain hurts!)
otherguy (Cameron Wilhelm ) wrote: Well, keep in mind that although SQL can do a lot in a single statement, it can't always do *everything* required for a business function in one statement. As I'm painfully aware of, but was hopeful about. Your hopes were sensible -- I believe that some SQL versions have an 'INTERSECT' analagous to the 'UNION' that would have done the job. (I've never used such an SQL, but I think they're out there somewhere!) It's a pity that MySQL doesn't have this extension. I can't thank you enough for your time and effort. I should be able to tweak this enough to make it usable. A pleasure; I enjoy this kind of thing. Besides, after pointing out that other solutions proposed weren't going to do it for you, I felt some obligation to come up with an alternative! Thanks again. Now I just have to beat up the system guys for making me do this :) You're welcome. Good luck with it. Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advanced Query Help (My brain hurts!)
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
Re: Advanced Query Help (My brain hurts!)
Thank you! 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,1,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (2,1,NC); INSERT
Re: Advanced Query Help (My brain hurts!)
otherguy wrote: That gets me halfway there Does it? In your original question, you'd indicated that you only wanted zips where *both* criteria were met -- enough CIRGs and enough CILTs. By using a UNION, you'll be getting zips where *either* is met. Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advanced Query Help (My brain hurts!)
OK - one more freebie :-) How about a TEMP table? You can create a temp table, fill it with the results of your UNION statement and then JOIN it with this new UPDATE query - that should get you what you need. Note that this is a workaround for MySQL - in other DBRMS, you can use a sub-SELECT to query the UNION result set - but for the purposes of MySQL, you're going to have to JOIN on a TEMP table. Good Luck! Dennis - Original Message - From: otherguy [EMAIL PROTECTED] To: MyLists [EMAIL PROTECTED] Cc: MySQL List [EMAIL PROTECTED] Sent: Saturday, June 28, 2003 6:17 PM Subject: Re: Advanced Query Help (My brain hurts!) Thank you! 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
Re: Advanced Query Help (My brain hurts!)
- Original Message - From: Bruce Feist [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Saturday, June 28, 2003 6:28 PM Subject: Re: Advanced Query Help (My brain hurts!) otherguy wrote: That gets me halfway there Does it? Yes, it does. In your original question, you'd indicated that you only wanted zips where *both* criteria were met -- enough CIRGs and enough CILTs. By using a UNION, you'll be getting zips where *either* is met. No. The key is that each independent query was returing the results he wanted - so, the UNION statement will simple append these two results into one long dataset - the WHERE clause, criteria, or even the number of records is really not affected. Thanks, Dennis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advanced Query Help (My brain hurts!)
On Saturday, June 28, 2003, at 03:43 PM, MyLists wrote: otherguy wrote: That gets me halfway there Does it? Yes, it does. No, I don't think it does, upon further consideration and testing... I thought it did b/c I read, and misinterpreted the UNION documentation In your original question, you'd indicated that you only wanted zips where *both* criteria were met -- enough CIRGs and enough CILTs. By using a UNION, you'll be getting zips where *either* is met. This is right. No. The key is that each independent query was returing the results he wanted - so, the UNION statement will simple append these two results into one long dataset - the WHERE clause, criteria, or even the number of records is really not affected. So is this. If I just needed to know that quota had been met for EITHER, then this would work perfectly for me. The key for my situation is that I need ONLY the records that exist in BOTH. Any other thoughts for this, or am I bumping up against the limits of SQL in general? Thanks again! -Cameron Wilhelm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advanced Query Help (My brain hurts!)
On Saturday, June 28, 2003, at 07:15 PM, Bruce Feist wrote: otherguy wrote: On Saturday, June 28, 2003, at 03:43 PM, MyLists wrote: That gets me halfway there BF: Does it? No, I don't think it does, upon further consideration and testing... I thought it did b/c I read, and misinterpreted the UNION documentation BF: In your original question, you'd indicated that you only wanted zips where *both* criteria were met -- enough CIRGs and enough CILTs. By using a UNION, you'll be getting zips where *either* is met. This is right. The key for my situation is that I need ONLY the records that exist in BOTH. Any other thoughts for this, or am I bumping up against the limits of SQL in general? Well, keep in mind that although SQL can do a lot in a single statement, it can't always do *everything* required for a business function in one statement. As I'm painfully aware of, but was hopeful about. [snip] Here's a sequence. [snip] I can't thank you enough for your time and effort. I should be able to tweak this enough to make it usable. Thanks again. Now I just have to beat up the system guys for making me do this :) Thank you, -Cameron Wilhelm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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,1,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (2,1,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (3,1,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (4,1,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (5,2,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (6,2,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (7,2,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (8,2,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (9,3,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (10,3,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (11,3,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (12,3,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
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,1,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (2,1,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (3,1,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (4,1,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (5,2,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (6,2,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (7,2,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (8,2,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (9,3,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (10,3,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (11,3,NC
