what about select customers.* from customers left join calls on (customers.date=calls.date) where customers.date="02/28/12";
of course date should be an index in both tables. I think it migth work On Wed, Feb 29, 2012 at 4:54 PM, LUCi5R <luc...@luci5r.com> 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") > > > > JW > > > > On Wed, Feb 29, 2012 at 1:02 PM, LUCi5R <luc...@luci5r.com> wrote: > > Guys, > > I've been working with MySQL for a while (been on & off this list over the > last 10 years or so); I'm definitely not a n00b and have worked with SQL > extensively. Used JOIN and all quite a bit ... but I haven't done > subqueries, union or nested joins. > > I'm completely stumped on this problem; and Google hasn't been helpful at > all. I'll try to be as descriptive as possible. > > I have 2 tables ... CUSTOMERS and CALLS. > Think of Customers table as your Directory. It has the customer's contact > information & some other information. In total about 20 fields in there. > The Calls table has only about 7 fields. Each time a customer calls in, the > conversation details gets recorded in this Calls table. > > The PHONE field is the key field that joins the CUSTOMERS & CALLS tables. > That is the only identifying key that gets written on the Calls record when > that customer calls. > > One thing to note -- It is possible for a customer to exist in the > CUSTOMERS > table, but not exist in the CALLS table; however, it is not possible for a > PHONE # to be in the CALLS table but not in CUSTOMERS table. Essentially, a > customer's record has to be created first in the CUSTOMERS table before a > call can be recorded from him in the CALLS table. > > Also, CALLS table can have multiple entries with same PHONE # (Customer > called many times - maybe even same day), but CUSTOMERS will only have a > single entry for a PHONE #. > > Here comes my problem ... > > I have a PHONE SEARCH box with the ability to define a date range; for > simplicity sake - we'll use just One Date instead of "DATE ... BETWEEN" for > now. > > When someone searches for a PHONE number, I want to show ALL the CUSTOMERS > that: > a. Were CREATED on that day (Date defined in Search Criteria) > b. Had CALLED in that day (Date defined in Search Criteria) > > The DATA that I need to pull up and show is in the CUSTOMERS table; not the > CALLS table -- so the DATA I need needs to come out of the CUSTOMERS table > matching on phone from both tables for the given DATE. > > In other words - any CUSTOMER that has the PHONE NUMBER which appears in > BOTH CUSTOMERS & CALLS table with the DATE defined should pull up. > > For the life of me - I can't get this to work!! > Let's take the date "02/28/12" for example sake. > > My biggest issue is ... using JOIN, I can pull up ... > a. ALL the phone/customers that appeared in the CALLS table with date > "02/28/12" > b. ALL the phone/customers that appeared in CALLS & CUSTOMERS with date > "02/28/12" > > BUT -- If there's a customer with date "02/28/12" who DOES NOT appear in > CALLS table at all - does NOT show up!! And that is because I'm using > CUSTOMERS.PHONE=CALLS.PHONE in the JOIN ON clause. So it obviously won't > pick up a record where the phone didn't exist in both tables. > > My initial query was: > > SELECT * FROM CUSTOMERS JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE > WHERE CUSTOMERS.DATE = "02/28/12" AND CALLS.DATE = "02/28/12" > > I've tried 100's of combinations of this query; many different OR, AND, > GROUP BY combinations --- but I can't pull up ALL records from CUSTOMERS > with DATE "02/28/12" and ALL records from CALLS with DATE "02/28/12" in a > single query. > > I've hit a wall here. > > Any ideas/suggestions/advice? > > 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 > > > > > > > > -- > ----------------------------- > Johnny Withers > 601.209.4985 > joh...@pixelated.net > >
