[PHP-DB] query of two tables returns too many rows, many more than the two tables contain
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) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] query of two tables returns too many rows, many more than the two tables contain
This is a common join issue. If you don't specify ANYTHING to connect between the two tables, it'll do one row from the first table, then ALL the rows from the second. Row #2 from the first, then ALL the rows from the second. If you had 600 rows in each table, you'd end up with 360,000 rows as a returned result. Chances are, you're just not being specific about what links the two tables. You might try something like this: SELECT dealers.account_no, dealers.DealerName, blackgate_users.User_Name, blackgate_users.DealerName FROM dealers, blackgate_users WHERE dealers.account_no blackgate_users.User_Name AND dealers.DealerName = blackgate_users.DealerName Or something like that. Just read the last thing you wrote.. Let me revise what I said then. You say you want everything where there's an entry in Dealers, but no corresponding entry in blackgate_users eh? That's an outer join. Try this (syntax happy with SQL Server, I don't use MySQL a lot so it might be slightly different): SELECT dealers.account_no, dealers.DealerName, blackgate_users.User_Name, blackgate_users.DealerName FROM dealers left join blackgate_users on dealers.DealerName = blackgate_users.DealerName WHERE blackgate_users.DealerName is null What this says is take everything in Dealers, left join it against blackgate_users (left join says to take everything from the left side.. And match against the right side but leave NULL entries where there's no match). Then we tell it that the field we want to compare is DealnerName in both cases. The WHERE clause says only show us where DealerName is null (meaning no corresponding record in blackgate_users). I think that'll do it for ya. -TG -Original Message- From: Chip Wiegand [mailto:[EMAIL PROTECTED] Sent: Thursday, November 11, 2004 4:28 PM To: PHP DB Subject: [PHP-DB] query of two tables returns too many rows, many more than the two tables contain 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) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] query of two tables returns too many rows, many more than the two tables contain
-Original Message- From: Chip Wiegand [mailto:[EMAIL PROTECTED] Sent: Thursday, November 11, 2004 3:28 PM To: PHP DB Subject: [PHP-DB] query of two tables returns too many rows, many more than the two tables contain I have two tables I want to get out the rows that are different between them. snip 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. === You want to do a join on the two tables, and make it conditional upon a known failing value (e.g. if when they DO match up, blackgate_users shouldn't be NULL - make that your condition). That will return just the rows in the first table that don't have a match. cheers, - Martin Norland, Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] query of two tables returns too many rows, many more than the two tables contain
Gryffyn, Trevor [EMAIL PROTECTED] wrote on 11/11/2004 01:39:37 PM: This is a common join issue. If you don't specify ANYTHING to connect between the two tables, it'll do one row from the first table, then ALL the rows from the second. Row #2 from the first, then ALL the rows from the second. snip Just read the last thing you wrote.. Let me revise what I said then. You say you want everything where there's an entry in Dealers, but no corresponding entry in blackgate_users eh? That's an outer join. Try this (syntax happy with SQL Server, I don't use MySQL a lot so it might be slightly different): SELECT dealers.account_no, dealers.DealerName, blackgate_users.User_Name, blackgate_users.DealerName FROM dealers left join blackgate_users on dealers.DealerName = blackgate_users.DealerName WHERE blackgate_users.DealerName 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 What this says is take everything in Dealers, left join it against blackgate_users (left join says to take everything from the left side.. And match against the right side but leave NULL entries where there's no match). Then we tell it that the field we want to compare is DealnerName in both cases. The WHERE clause says only show us where DealerName is null (meaning no corresponding record in blackgate_users). I think that'll do it for ya. -TG -Original Message- From: Chip Wiegand [mailto:[EMAIL PROTECTED] Sent: Thursday, November 11, 2004 4:28 PM To: PHP DB Subject: [PHP-DB] query of two tables returns too many rows, many more than the two tables contain 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) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
