List:General Discussion« Previous MessageNext Message »
From:Alec.Cawley Date:July 9 2004 3:49pm
Subject:Re: anyone help with this query? Returning tooooo many results
View as plain text  
"Aaron Wolski" <aaronjw@stripped> wrote on 09/07/2004 16:33:27:

> Hi all,
> 
> Having a problem with a query that's returning 486,057 results when it
> most definitely should NOT be doing that.
> 
> I have two tables:
> 
> 1 for a list of customers that purchase product A, another for customers
> who purchased product B.
> 
> Columns are:
> 
> Id
> First
> Last
> Email
> 
> I am trying to compare table 1 to table 2 to get a result set that gives
> me the contact info (table columns) for those whose email addresses in
> table 1 DON'T EQUAL those in table two.
> 
> In table one I have 2026 records
> In table two I have 240 records
> 
> The query is this:
> 
> SELECT * FROM producta_customers AS t1, productb_customers AS t2 WHERE
> t1.email != t2.email
> 
> When I do this query. I get 486,057 results returne.
> 
> Where am I going wrong? Any ideas?

You have not clearly understood the effect of a simple join. This creates 
(logically speaking) a table containing every possible combination fro t1 
and t2. Your WHERE statement is then stripping out those few rows where 
the email addresses match.

I am not quite sure what you are trying to do. I think you are trying to 
get the details of those customers who have bought A but not B. This is 
the province of the LEFT JOIN. Try

SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.email = t2.email WHERE t2.email IS 
NULL 

This logically creates a table with a record for every customer who has 
bought both A and B PLUS a record for every customer who bough A but not 
B, with the fields for the latter being null. The WHERE statement picks 
out only the latter group of records, which are what you want. Don't worry 
about the purely virtual huge table created in the middle - MySQL can 
optimise it out. Trust In The (MySQL) Force.

        Alec
Thread
anyone help with this query? Returning tooooo many resultsAaron Wolski9 Jul
  • Re: anyone help with this query? Returning tooooo many resultsMartijn Tonies9 Jul
  • Re: anyone help with this query? Returning tooooo many resultsAlec.Cawley9 Jul
  • Re: anyone help with this query? Returning tooooo many resultsPhilippe Poelvoorde9 Jul
RE: anyone help with this query? Returning tooooo many resultsVictor Pendleton9 Jul
Re: anyone help with this query? Returning tooooo many resultsSGreen9 Jul
  • RE: anyone help with this query? Returning tooooo many resultsAaron Wolski9 Jul
RE: anyone help with this query? Returning tooooo many resultsSGreen9 Jul
  • RE: anyone help with this query? Returning tooooo many resultsAaron Wolski9 Jul