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 SELECT * FROM CUSTOMERS 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 table
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: SELECT customers.* FROM customers 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"; SELECT customers.* FROM customers 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 index. Regards, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
