Well well...
That worked too!
Damn... this is starting to make life easier :)
Thanks again. Very much appreciated!!!
Aaron
> -----Original Message-----
> From: SGreen@stripped [mailto:SGreen@stripped]
> Sent: July 9, 2004 2:00 PM
> To: Aaron Wolski
> Cc: mysql@stripped
> 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"
> <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
> >
>
>
>
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
