I have 2 tables with the fields:  FiscalYear, Account, Region, Program

I want to treat these values as if they are a concatenated key.  I want to
compare
2 tables to see if the one table has any concatenated key in that table that
does not
exist in the other.  I need to do this without modifiying the tables with keys
extra fields etc.  I want to do this with just Transact SQL and not
using other languages.  Any sugestions?

Thanks - Ed

Ed,

You do not need to concatenate columns to do this.

select *
from dbo.t1
where not exists (
select *
from dbo.t2
where
   t2.FiscalYear = t1.FiscalYear
   and te.Account = t1.Account
   and t2.Region = t1.Region
   and t2.Program = t1.Program
);


AMB

Show quoteHide quote

Columns are not fields; you are going to screw up a lot things until
you learn that.  Please post DDL, so that people do not have to guess
what the keys, constraints, Declarative Referential Integrity, data
types, etc. in your schema are. Sample data is also a good idea, along
with clear specifications.  It is very hard to debug code when you do
not let us see it.

Then there is the question as to why you have two tables with the same
structure, in violation of some basic RDBMS rules?  This is a pretty
good sign that you have serious atrtribute splitting problems and a
non-relational schema.

There is no such term in RDBMS, or in SQL.  Did you mean a compound
key?  You still think that data is physically contigous and stored as
text -- the COBOL model!

The *right* answer is to combine these vague tables into a single table
with a column for the values of the attribute you used to split them.

The kludge is below -- it also gives some ideas about the ISO-11179
rules for data element names that you did not follow:

SELECT S1.*, S2.*
  FROM SplitNamelessTable AS S1
           FULL OUTER JOIN
        SplitNamelessTable AS S2
           ON S1.fiscalyear = S2.fiscalyear
             AND S1.foobar_account = S2.foobar_account
             AND S1.region_id = S1.region_id
             AND S1.program_name = S2.program_name
WHERE COALESCE (S1.fiscalyear, S1.foobar_account, S1.region_id,
S1.program_name) IS NULL
     OR COALESCE (S2.fiscalyear, S2.foobar_account, S2.region_id,
S2.program_name) IS NULL;

Since you did not bother to tell us about NULLs and how they affect
matching rules, data types and all that other *vital information*, this
is only a guess.

There is also a version with EXISTS() predicates that has been posted
several times.

Well if the 2 tables are A and B, then its ( A Union B ) - (A Intersect B)

SQL Server 2005's readable version of Joe's Solution.

(Select * from A
UNION
Select * from B)
EXCEPT
(select * from A
INTERSECT
select * from B)

Untested, but should work :)

Ands the SQL-92 version would be

SELECT  * FROM A OUTER UNION SELECT * FROM B;

but n obody has implemented the OUTER UNION.

SQLXML 4.0 "timestamp" updategram question
Identity keys in a One-to-Zero-or-Many relationship
Insert in every ID
Exception_Access_Violation Error?
Incorrect information from a "View"