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. In a future release (5.0, if I remember correctly) you'll be able to do it with views. For now, I believe you'll need to use temporary tables and multiple SQL statements.
Here's a sequence. None of the below has been tested; it will likely require some tweaking. In particular, the UPDATEs I show you, if they work at all, will only work in relatively new versions of MySQL (4.1 and higher, maybe?). In versions 4.0.x, there are other techniques that I think will work similarly, although I haven't used them; the sytnax is different, though.
CREATE TEMPORARY TABLE cirg
SELECT z.quota_zip2.zipcode, SUM(c.quota_actual) AS cirg_actual, SUM(c.quota_limit) AS cirg_limit
FROM quota_zip2 z INNER JOIN quota_control2 c ON z.agent_code = c.agent_code AND z.appl = c.appl
WHERE c.appl = 'CIRG'
GROUP BY z.zipcode
HAVING cirg_actual >= cirg_limit;
CREATE TEMPORARY TABLE cilt
SELECT z.quota_zip2.zipcode, SUM(c.quota_actual) AS cilt_actual, SUM(c.quota_limit) AS cilt_limit
FROM quota_zip2 z INNER JOIN quota_control2 c ON z.agent_code = c.agent_code AND z.appl = c.appl
WHERE c.appl = 'CILT'
GROUP BY z.zipcode
HAVING cilt_actual >= cilt_limit;
CREATE TEMPORARY TABLE zips SELECT cirg.zipcode, cirg_actual, cirg_limit, cilt_actual, cilt_limit FROM cirg INNER JOIN cilt ON cirg.zipcode = cilt.zipcode ORDER BY cirg.zipcode;
SELECT * FROM zips;
UPDATE listmaster SET status = 'WD' WHERE zipcode IN (SELECT zipcode FROM zips);
DROP TABLE zips; DROP TABLE cirg; DROP TABLE cilt;
When views become available, you will be able to create 'cirg', 'cilt', and 'zips' views like the temporary tables above and just leave them out there; then you will always be able to execute the SELECT above as a single statement.
Another approach, which is slightly simpler but harder to understand, is to use a common temporary table for the CIRGs and CILTs.
CREATE TEMPORARY TABLE zipapp
SELECT z.quota_zip2.zipcode, z.appl, SUM(c.quota_actual) AS sum_actual, SUM(c.quota_limit) AS sum_limit
FROM quota_zip2 z INNER JOIN quota_control2 c ON z.agent_code = c.agent_code AND z.appl = c.appl
WHERE c.appl IN ('CIRG', 'CILT')
GROUP BY z.zipcode, z.appl
HAVING sum_actual >= sum_limit;
CREATE TEMPORARY TABLE zips
SELECT cirg.zipcode, cirg.sum_actual, cirg.sum_limit, cilt.sum_actual, silt.sum_limit
FROM zipapp cirg INNER JOIN zipapp zilt ON cirt.zipcode = cilt.zipcode
WHERE cirt.appl = 'CIRT' AND cilt.appl = 'CILT'
ORDER BY z.zipcode;
SELECT * FROM zips;
UPDATE listmaster SET status = 'WD' WHERE zipcode IN (SELECT zipcode FROM zips);
DROP TABLE zips; DROP TABLE zipapp;
Another simplification that you can introduce would be to drop all tables involved in a single statement (i.e., "DROP TABLE zips, zipapp"), but that reduces portability.
Bruce Feist
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
