Stupid wrapping helped me to make a simple mistake. I wrote
On 3/1/2012 10:40 AM, LUCi5R wrote:
> SELECT *
> FROM CUSTOMERS
> LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND
> CUSTOMERS.DATE=CALLS.DATE = "02/28/12"
But I meant it to be
LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE
AND CALLS.DATE = "02/28/12"
But based on your description:
> a) The customer was created on given date (Eg:- '02/28/12')
> b) The customer called on given date and the call was recorded in the Calls
There are possibly two different dates at play, a "creation date" (customers.date) and an
activity date (calls.date). Therefore, we need to list them separately. Also, you said
you wanted just the CUSTOMERS records (without any call details) so I assume you only
want to see a single copy of each customer. This would work best using the EXISTS pattern
I provided last with a simple modification:
WHERE EXISTS (SELECT * FROM calls WHERE CUSTOMERS.PHONE = CALLS.PHONE AND
CUSTOMERS.DATE=CALLS.DATE = "activity date")
AND customers.date="creation date"
There are many other ways to find this same set of data. Here is a two-step process using
an indexed temporary table (technically, it's a 3-step process as you need to drop the
explicit temp table, too).
CREATE TEMPORARY TABLE tmp_custs(key(phone) USINB BTREE) ENGINE=MEMORY SELECT DISTINCT
phone FROM CALLS WHERE date="activity date";
INNER JOIN tmp_custs ON tmp_custs.phone = customers.phone
WHERE customers.date = "create date";
DROP TEMPORARY TABLE tmp_custs;
By default the MEMORY engine creates all indexes as HASH indexes. So in order to replace
the ="activity date" comparison with any sort of ranged comparison, you need a BTREE
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN