Concat key Query 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 ke
ys
extra fields etc. I want to do this with just Transact SQL and not
using other languages. Any sugestions?
Thanks - EdEd,
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
"Ed" wrote:

> 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 th
at
> 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
>|||>> 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.
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!
QL 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.|||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 :)
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||>> 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. <<
Ands the SQL-92 version would be
SELECT * FROM A OUTER UNION SELECT * FROM B;
but n obody has implemented the OUTER UNION.sqlsql