[EMAIL PROTECTED] wrote on 11/11/2004 02:05:17 PM: <snip> > 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
Thanks for the help. That gets me much closer. I did a count in both tables and figured there should be 121 rows returned by the query. The above select statement gets me 141 rows returned. With a little sleuthing around in there I will probably figure out what the extra 10 rows are. Thanks you very much. Regards, Chip > 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
