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

You’re correct .. that is what I was getting with the LEFT JOIN and
therefore it wasn’t the correct answer. 

I was able to get the correct answer using UNION ALL, however, like you
said, I needed 2 queries in that case. One to get the PHONE numbers from
both tables … and a 2nd query to get all the CUSTOMERS matching the PHONE
numbers from the CUSTOMERS table. Although I still have a few issues with
that (since I can’t use a VIEW cause VIEWS don’t allow subqueries in SELECT
statements; and I don’t really want to create a new table every time this
query is run) … but anyhow, this is the UNION ALL query that got the correct
“86” records result:

SELECT PHONE FROM (
 (SELECT PHONE,DATE FROM CUSTOMERS)
UNION ALL
 (SELECT PHONE,DATE FROM CALLS)
)
results
WHERE DATE = “02/28/12”
GROUP BY PHONE;

I would still like to explore the possibility of doing this using 1 single
query which gives me the results I need – rather than first getting the
correct PHONE numbers; inserting them in a temporary table; and then pulling
records out of CUSTOMERS matching those PHONE numbers. 

THANKS!

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


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

After looking at this again, the query you are using;

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")

Would return all customers in the customer's table created on 2/28/12 WITH
no calls or a call on 2/28.

Maybe you should try..

SELECT * 
FROM CUSTOMERS 
WHERE CUSTOMERS.DATE = "02/28/12" 

UNION

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

Basically, you have to do two queries to get the data you want. The WHERE
customers.date=2/28 is only getting those customers created on 2/28
regardless if they had a call or not on 2/28. So if you had a customer
created on 2/27 and a call on 2/28, the query we were using is not going to
pick that customer up.

If you want duplicate customers from the second query in the UNION above,
you can use UNION ALL instead of just UNION between the queries.

-JW

On Wed, Feb 29, 2012 at 4:54 PM, LUCi5R <luci5r@stripped> wrote:
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




-- 
-----------------------------
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