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: luc...@luci5r.com
w: http://www.luci5r.com
From: Johnny Withers [mailto:joh...@pixelated.net]
Sent: Wednesday, February 29, 2012 1:30 PM
To: luc...@luci5r.com
Cc: mysql@lists.mysql.com
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
