On Thu, Mar 1, 2012 at 8:57 AM, Shawn L Green <shawn.l.gr...@oracle.com>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: 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<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. ;-)
