Well well... That worked too!
Damn... this is starting to make life easier :) Thanks again. Very much appreciated!!! Aaron > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: July 9, 2004 2:00 PM > To: Aaron Wolski > Cc: [EMAIL PROTECTED] > Subject: RE: anyone help with this query? Returning tooooo many results > > > 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" > <[EMAIL PROTECTED] To: > <[EMAIL PROTECTED]> > z.com> cc: > <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > Sent: July 9, 2004 12:17 PM > > To: Aaron Wolski > > Cc: [EMAIL PROTECTED] > > 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" > > <[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] > > > > > > > > > > > -- > 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]
