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:chip.wiegand@xxxxxxxxxx]
> Sent: Thursday, November 11, 2004 4:28 PM
> To: PHP DB
> Subject: 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
