You have written a cross-product join. This is what happened but with a
much smaller example:
Assume you have two tables: Colors and Sizes
CREATE TABLE Colors (
id int auto_increment primary key
, name varchar(10)
);
CREATE TABLE Sizes (
id int auto_increment primary key
, abbr varchar(6)
);
And you populate them with the following data:
INSERT Colors (name) VALUES ('Red'),('Blue'),('Yellow'),('Violet');
INSERT Sizes (abbr) VALUES ('XS'),('M'),('L'),('XL'),('XXL');
This query:
SELECT colors.name, sizes.abbr FROM Colors, Sizes;
Returns:
+--------+------+
| name | abbr |
+--------+------+
| Red | XS |
| Blue | XS |
| Yellow | XS |
| Violet | XS |
| Red | M |
| Blue | M |
| Yellow | M |
| Violet | M |
| Red | L |
| Blue | L |
| Yellow | L |
| Violet | L |
| Red | XL |
| Blue | XL |
| Yellow | XL |
| Violet | XL |
| Red | XXL |
| Blue | XXL |
| Yellow | XXL |
| Violet | XXL |
+--------+------+
20 rows in set (0.04 sec)
Notice that every possible combination between color and size is listed?
When you wrote your query, you also asked the query engine to create every
possible combination between each customer in the first table and every
customer in the second table. That resulted in 486,240 matches. Then the
engine applied your WHERE condition to all of those matches and ELIMINATED
of all of the rows where the email addresses were the SAME between the two
tables so you wound up with *only* 486,057 combinations of customers
between the two tables where their addresses were different.
I think what you wanted to find was all of the rows in one table that
didn't match any rows in the other table. You can do it with this
statement:
SELECT a.ID, a.First, a.Last, a.Email
FROM producta_customers a
LEFT JOIN productb_customers b
ON a.email=b.email
WHERE b.id is null
This will give you all of the records in producta_customers that DO NOT
have a matching email address in the productb_customers table.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Aaron Wolski"
<[EMAIL PROTECTED] To: <[EMAIL PROTECTED]>
z.com> cc:
Fax to:
07/09/2004 11:33 Subject: anyone help with this query?
Returning tooooo many results
AM
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?
Thanks so much for the help!
Aaron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
