List:General Discussion« Previous MessageNext Message »
From:LUCi5R Date:February 29 2012 7:02pm
Subject:Getting data from 2 tables if records have same date!
View as plain text  
Guys,

I've been working with MySQL for a while (been on & off this list over the last 10
years or so); I'm definitely not a n00b and have worked with SQL extensively. Used JOIN
and all quite a bit ... but I haven't done subqueries, union or nested joins. 

I'm completely stumped on this problem; and Google hasn't been helpful at all. I'll try to
be as descriptive as possible. 

I have 2 tables ... CUSTOMERS and CALLS. 
Think of Customers table as your Directory. It has the customer's contact information
& some other information. In total about 20 fields in there. 
The Calls table has only about 7 fields. Each time a customer calls in, the conversation
details gets recorded in this Calls table.

The PHONE field is the key field that joins the CUSTOMERS & CALLS tables. That is the
only identifying key that gets written on the Calls record when that customer calls. 

One thing to note -- It is possible for a customer to exist in the CUSTOMERS table, but
not exist in the CALLS table; however, it is not possible for a PHONE # to be in the
CALLS table but not in CUSTOMERS table. Essentially, a customer's record has to be
created first in the CUSTOMERS table before a call can be recorded from him in the CALLS
table.  

Also, CALLS table can have multiple entries with same PHONE # (Customer called many times
- maybe even same day), but CUSTOMERS will only have a single entry for a PHONE #. 

Here comes my problem ... 

I have a PHONE SEARCH box with the ability to define a date range; for simplicity sake -
we'll use just One Date instead of "DATE ... BETWEEN" for now. 

When someone searches for a PHONE number, I want to show ALL the CUSTOMERS that:
a. Were CREATED on that day (Date defined in Search Criteria)
b. Had CALLED in that day (Date defined in Search Criteria)

The DATA that I need to pull up and show is in the CUSTOMERS table; not the CALLS table --
so the DATA I need needs to come out of the CUSTOMERS table matching on phone from both
tables for the given DATE.

In other words - any CUSTOMER that has the PHONE NUMBER which appears  in BOTH CUSTOMERS
& CALLS table with the DATE defined should pull up. 

For the life of me - I can't get this to work!!
Let's take the date "02/28/12" for example sake. 

My biggest issue is ... using JOIN, I can pull up ...
a. ALL the phone/customers that appeared in the CALLS table with date "02/28/12"
b. ALL the phone/customers that appeared in CALLS & CUSTOMERS with date "02/28/12"

BUT -- If there's a customer with date "02/28/12" who DOES NOT appear in CALLS table at
all - does NOT show up!! And that is because I'm using CUSTOMERS.PHONE=CALLS.PHONE in the
JOIN ON clause. So it obviously won't pick up a record where the phone didn't exist in
both tables. 

My initial query was:

SELECT * FROM CUSTOMERS JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE
WHERE CUSTOMERS.DATE = "02/28/12" AND CALLS.DATE = "02/28/12"

I've tried 100's of combinations of this query; many different OR, AND, GROUP BY
combinations --- but I can't pull up ALL records from CUSTOMERS with DATE "02/28/12" and
ALL records from CALLS with DATE "02/28/12" in a single query. 

I've hit a wall here. 

Any ideas/suggestions/advice?

THANKS

~~
LUCi5R
e:  luci5r@stripped
w:  http://www.luci5r.com


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