Concat key Query Question  
Author Message
larryfergy





PostPosted: Fri Jun 30 10:06:29 CDT 2006 Top

SQL Server Developer >> 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 keys
extra fields etc. I want to do this with just Transact SQL and not
using other languages. Any sugestions?

Thanks - Ed

SQL Server197  
 
 
--CELKO--





PostPosted: Fri Jun 30 10:06:29 CDT 2006 Top

SQL Server Developer >> Concat key Query Question >> I have 2 tables with the fields [sic]: FiscalYear, Account, Region, Program <<

Columns are not fields; you are going to****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.

 
 
AlejandroMesa





PostPosted: Fri Jun 30 10:06:01 CDT 2006 Top

SQL Server Developer >> Concat key Query Question 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



> 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
>
 
 
Omnibuzz





PostPosted: Sat Jul 01 00:51:01 CDT 2006 Top

SQL Server Developer >> Concat key Query Question 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/

 
 
--CELKO--





PostPosted: Sat Jul 01 15:25:35 CDT 2006 Top

SQL Server Developer >> Concat key Query Question >> 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.