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

 

I'm trying to understand LEFT JOIN as we go - but it's not working. 

 

This query 

 

SELECT * 

FROM CUSTOMERS 

LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE 

WHERE CUSTOMERS.DATE = "02/28/12" AND (CALLS.PHONE IS NULL OR CALLS.DATE =
"02/28/12")

 

Is giving me some results which I'm not quite sure what they are - but it's
not the right results. 

 

The way I'm testing is, on 02/28/12 I had 57 Customers created in the
CUSTOMERS table. 

I also had a total of 105 Calls recorded in the CALLS table. Some calls were
from the same customers more then once. 

 

Essentially, I need the result to be 86 which I got from some manual
calculations. Out of those 86 records, 1 record is in the CUSTOMERS table
but not in the CALLS table. The other 85 were in both tables. 

 

The above LEFT JOIN query gave me 69 records and quite a few duplicate
entries. I'm trying to dissect it to understand what exactly it selected. 

 

Thanks!

 

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

 

 

From: Johnny Withers [mailto:johnny@stripped] 
Sent: Wednesday, February 29, 2012 1:30 PM
To: luci5r@stripped
Cc: mysql@stripped
Subject: Re: Getting data from 2 tables if records have same date!

 

Sounds like you need to LEFT JOIN:

 

SELECT * 

FROM CUSTOMERS 

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:

 

SELECT * 

FROM CUSTOMERS 

LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE 

WHERE CUSTOMERS.DATE = "02/28/12" AND (CALLS.PHONE IS NULL OR CALLS.DATE =
"02/28/12")

 

JW

 

On Wed, Feb 29, 2012 at 1:02 PM, LUCi5R <luci5r@stripped> wrote:

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



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql





 

-- 
-----------------------------
Johnny Withers
601.209.4985
johnny@stripped


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