List:General Discussion« Previous MessageNext Message »
From:Shawn L Green Date:March 1 2012 4:20pm
Subject:Re: Getting data from 2 tables if records have same date!
View as plain text  
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




Thread
Getting data from 2 tables if records have same date!LUCi5R29 Feb
  • Re: Getting data from 2 tables if records have same date!Johnny Withers29 Feb
    • RE: Getting data from 2 tables if records have same date!LUCi5R29 Feb
      • Re: Getting data from 2 tables if records have same date!Johnny Withers29 Feb
        • RE: Getting data from 2 tables if records have same date!LUCi5R1 Mar
      • Re: Getting data from 2 tables if records have same date!AndrĂ©s Tello1 Mar
      • Re: Getting data from 2 tables if records have same date!Shawn L Green1 Mar
        • Re: Getting data from 2 tables if records have same date!David Giragosian1 Mar
        • RE: Getting data from 2 tables if records have same date!LUCi5R1 Mar
          • Re: Getting data from 2 tables if records have same date!Shawn L Green1 Mar
    • Re: Getting data from 2 tables if records have same date!hsv1 Mar
      • RE: Getting data from 2 tables if records have same date!LUCi5R1 Mar