List:General Discussion« Previous MessageNext Message »
From:Shawn L Green Date:March 1 2012 2:57pm
Subject:Re: Getting data from 2 tables if records have same date!
View as plain text  
On 2/29/2012 5:54 PM, LUCi5R 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")
>

Try this:

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

This will give you a list of all customers for a given date and a list 
of every call they made on that date.  If a customer made no calls on a 
date, then all of the columns for that table will be NULL.

If you only want a list of customers and details about the calls on a 
date then an INNER JOIN is appropriate.  If you want to see the full 
list of customers and any calls on that date use this:

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

If you only want a list of customers that made any calls on a given date, you can use the
EXISTS comparator like this:

SELECT customers.*
FROM customers
WHERE EXISTS (SELECT * FROM calls WHERE CUSTOMERS.PHONE = CALLS.PHONE AND
CUSTOMERS.DATE=CALLS.DATE = "02/28/12")

http://dev.mysql.com/doc/refman/5.5/en/exists-and-not-exists-subqueries.html

It's possible to get you any combination of data you want, we just need you to clarify the
relationship you are trying to find and how much data you really want to get back.

NOTE: the name of the column date is using a reserved word. You may want to enclose it in
backticks to avoid confusion as in `date`. Also, the standard MySQL syntax for date
literals uses ISO notation. So instead of using "02/28/12" (using double quotes) I
expected to see '2012-02-28' (using single quotes)

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