-----Original Message----- From: Shawn L Green [mailto:shawn.l.gr...@oracle.com] Sent: Thursday, March 01, 2012 6:57 AM To: luc...@luci5r.com Cc: 'Johnny Withers'; mysql@lists.mysql.com 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: luc...@luci5r.com w: http://www.luci5r.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
