Concat key Query Question

SQL Apprentice Question
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?

Celko Answers
>> I have 2 tables with the fields [sic]: FiscalYear, Account, Region, Program <<


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.



>> I want to treat these values as if they are a concatenated key [sic]. <<


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!


>> I want to compare 2 tables to see if the one table has any concatenated key [sic] in that table that does not exist in the other. I need to do this without modifiying the tables with keys extra fields [sic] etc. I want to do this with just Transact SQL and not using other languages. Any sugestions? <<


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.