Sounds like you need to LEFT JOIN:
LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CALLS.DATE = "02/28/12"
WHERE CUSTOMERS.DATE = "02/28/12"
But that would only get customers created on 2/28 AND having a call on 2/28
OR not call at all on 2/28.
This would give you customers created on 2/28 with no calls AND customers
created on 2/28 with a call on 2/28:
LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE
WHERE CUSTOMERS.DATE = "02/28/12" AND (CALLS.PHONE IS NULL OR CALLS.DATE =
On Wed, Feb 29, 2012 at 1:02 PM, LUCi5R <luci5r@stripped> wrote:
> 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
> When someone searches for a PHONE number, I want to show ALL the CUSTOMERS
> 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
> b. ALL the phone/customers that appeared in CALLS & CUSTOMERS with date
> 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?
> e: luci5r@stripped
> w: http://www.luci5r.com
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql