What happened is called a Cartesian product. You basically asked for a
combination of every value from one table matched to every value from the
other table. In your case you did have an "except"-type condition so you
eliminated a few of the matches.
Let me explain it this way: Imagine you have two identical lists of names.
Each list is 200 names long and there are no duplicates within the list.
The query you just wrote would combine each of the first 200 names with
each one of the other 199 names from the other table that didn't match so
that your final results would be (200x199) or 39800 records. Way more than
the 400 you started with, isn't it....
What you want to do is to write a query that lists every record from the
dealers table OPTIONALLY matched up to rows on the blackgate_users table
wherever the names match. This is what the LEFT JOIN operator was
invented to do (you were using an INNER JOIN). Now, to find the
non-matches you look for rows from the blackgate_users table where a
normally non-null value IS NULL. Because you want ALL of the rows from the
dealers table but only some of the rows from the blackgate_users table
(you declare that when you say LEFT JOIN), the query engine fills in the
missing rows from the "right hand" table (in this case blackgate_users)
with all NULL values.
SELECT dealers.account_no, dealers.DealerName, blackgate_users.User_Name,
blackgate_users.DealerName
FROM dealers
LEFT JOIN blackgate_users
ON dealers.account_no = blackgate_users.User_Name
WHERE blackgate_users.account_no IS NULL
So by looking for only those rows where the "right hand" table contains
NULL values, you find the non-matching rows. Get it?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Chip Wiegand <[EMAIL PROTECTED]> wrote on 11/11/2004 04:29:08 PM:
> I have two tables I want to get out the rows that are different between
> them. The results I am getting is almost 50,000 rows, but the two
tables,
> combined, contain only about 600 rows total. Here is the select
statement
> -
>
> SELECT dealers.account_no, dealers.DealerName,
blackgate_users.User_Name,
> blackgate_users.DealerName
> FROM dealers, blackgate_users
> WHERE dealers.account_no NOT
> LIKE blackgate_users.User_Name
>
> in these tables the
> dealers.account_no is the same data as the blackgate_users.User_Name
> dealers.DealerName is the same data as the blackgate_users.DealerName
> I just want the rows that are in the dealers table but not in the
> blackgate_users table.
>
> Thanks for any help,
> Chip Wiegand
> Computer Services
> Simrad, Inc
> 425-778-8821
> 425-771-7211 (FAX)
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
