Unusual Query

[Tim Morrison]

SQL Server 2000

I need to compare 2 tables as follows:

Table 1 - Accounts
Acct#, Account Name

Table 2 - Ledger
(Among Others)
Acct#,AccountName

I would like to create a view where the account number matches in both tables but the account name does not.

I.e.

Table 1:
5000 Maintenance

Table 2:
5000 Maintenance
5000 Maintenance
5000 Building Maintenance

my query view would display:
5000 Building Maintenance

indicating there is a "Bad Record" and I would do some processing from there.

FYI - I know I could prevent this easily, but I am actually comparing several databases with Table 2 data with a central Table 1 database.

TIA

--
Tim Morrison

--------------------------------------------------------------------------------

Vehicle Web Studio - The easiest way to create and maintain your vehicle related website.
http://www.vehiclewebstudio.com

[Anith Sen]

You can do a simple EXISTS check like:

SELECT *
FROM tbl2
WHERE NOT EXISTS ( SELECT *
FROM tbl1
WHERE tbl1.Acct# = tbl2.Acct#
AND tbl1.acc_name = tbl2.acc_name );

--
- Anith
( Please reply to newsgroups only )

[Sjaak van Esdonk]

This should do the trick:

SELECT
table1.acct,
Table2.name

FROM
table1,
table2

where
table1.acct = Table2.acct
AND table1.name <> Table2.name