List:General Discussion« Previous MessageNext Message »
From:SGreen Date:July 9 2004 6:00pm
Subject:RE: anyone help with this query? Returning tooooo many results
View as plain text  
Aaron,

That would be an INNER JOIN situation:

SELECT a.ID, a.First, a.Last, a.Email
FROM producta_customers a
INNER JOIN productb_customers b
       ON a.email=b.email

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine




                                                                                          
                            
                      "Aaron Wolski"                                                      
                            
                      <aaronjw@martekbi        To:       <SGreen@stripped>     
                                     
                      z.com>                   cc:       <mysql@stripped> 
                                     
                                               Fax to:                                    
                            
                      07/09/2004 01:10         Subject:  RE: anyone help with this query?
Returning tooooo many        
                      PM                        results                                   
                            
                                                                                          
                            
                                                                                          
                            




Hi all,

First... I just want tot hank everyone for their help and explanations
of how I was going wrong, and the measures to correct my logic!

Great, great advice.

Shawn's solution worked absolutely wonderful for my needs.

My next question is how do I reverse the query so that I can get all of
those customers who DO have email address that matches in each table?

Thanks again guys. Very much appreciated!

Aaron

> -----Original Message-----
> From: SGreen@stripped [mailto:SGreen@stripped]
> Sent: July 9, 2004 12:17 PM
> To: Aaron Wolski
> Cc: mysql@stripped
> Subject: Re: anyone help with this query? Returning tooooo many
results
>
>
> 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
>
>
>
>
>
>
>
> --
> 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