"Aaron Wolski" <[EMAIL PROTECTED]> 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
