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 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]
