List:General Discussion« Previous MessageNext Message »
From:SGreen Date:July 9 2004 4:17pm
Subject:Re: anyone help with this query? Returning tooooo many results
View as plain text  
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"                                                      
                            
                      <aaronjw@martekbi        To:       <mysql@stripped> 
                                     
                      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/mysql?unsub=1






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