RE: [PHP-DB] query of two tables returns too many rows, many more than the two tables contain

"Gryffyn, Trevor" <TGryffyn@air-cargo-inc.com> 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:chip.wiegand@simrad.com] > > 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 > > > >