List:General Discussion« Previous MessageNext Message »
From:LUCi5R Date:March 1 2012 3:40pm
Subject:RE: Getting data from 2 tables if records have same date!
View as plain text  
-----Original Message-----
From: Shawn L Green [mailto:shawn.l.green@stripped] 
Sent: Thursday, March 01, 2012 6:57 AM
To: luci5r@stripped
Cc: 'Johnny Withers'; mysql@stripped
Subject: Re: Getting data from 2 tables if records have same date!

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

~~~~~~~~~~~~~~

Shawn,

I still need to try your queries but I wanted to quickly get back to you on
a couple of things. 

You're correct! The DATE column is a reserved word and in my queries I do
indeed use `backticks`. Interestingly, I never knew that's what they are
called!!! I used to call that symbol `Grave Accent` ... never heard it being
called `backticks`. Good to know :)

Secondly, unfortunately this DATE field came from an original FoxPro
Database (DBF) which was an Char (8) field and got translated into mysql as
such. So unfortunately it's not a DATE field ... it's a Char (8) field. For
simplicity, I've been using Date = "02/28/12" to explain my queries here,
but in reality, in my program, I've actually been using the STR_TO_DATE()
function as such ... 

STR_TO_DATE(`DATE`, '%m/%d/%Y') = STR_TO_DATE('02/2/12', '%m/%d/%Y')

Lastly, you're correct again, I don't actually use the Double Quotes around
the dates ... I do use Single Quotes. 

In regards to the relationship/data that I'm looking for ... this is what
I'm looking for ...

I need to pull the records from the CUSTOMERS table, where 
a) The customer was created on given date (Eg:- '02/28/12')
b) The customer called on given date and the call was recorded in the Calls
table (Eg:- '02/28/12')

Let me give your queries a shot & understand them!

THANKS!!

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



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