List:General Discussion« Previous MessageNext Message »
From:David Giragosian Date:March 1 2012 3:03pm
Subject:Re: Getting data from 2 tables if records have same date!
View as plain text  
On Thu, Mar 1, 2012 at 8:57 AM, Shawn L Green <shawn.l.green@stripped>wrote:

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



Another gem from Shawn. ;-)

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