SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Comparing columns for equality
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 10/04/2013 :  11:10:10  Show Profile  Reply with Quote
HI all,

I've got a DB that came with no FK's and no docu, so I need to rebuild those relationships. To do that I need to compare 2+ ID columns in different tables (suspected PK and suspected FK) for matches.

Ideally I'd like to see what % of the (suspected) FK is comprised of those values in the (suspected) PK. Is an inner join the best way to achieve this?

Also - and I this probably involves a big Cartesian join as there are 100 tables - is there a way to loop through ALL columns named '%id' throughout the DB, with a result set like,

'ColumnName' - 'ColumnName' - 'MatchingRowCount' - '%OfFKMatched'

I'm not worried about performance at this point because the DB is only 36Mbs,

Cheers,

JB

James K
Flowing Fount of Yak Knowledge

3593 Posts

Posted - 10/04/2013 :  11:37:01  Show Profile  Reply with Quote
If the tables are using surrgate keys for primary key, this approach can lead misleading results. Think of the case where the PK's are integer numbers starting at 1. Completely dissimilar and unrelated tables could have matching values.

Regardless, if you want to compare columns from two tables and see what percentage matches, you can do the following:
SELECT
	COUNT(a.colA),
	COUNT(b.colB),
	100.0*SUM(CASE WHEN a.colA IS NOT NULL AND b.colB IS NOT NULL THEN 1 ELSE 0 END) /NULLIF(COUNT(b.colB),0) AS AHasMatchesForB,
	100.0*SUM(CASE WHEN a.colA IS NOT NULL AND b.colB IS NOT NULL THEN 1 ELSE 0 END) /NULLIF(COUNT(a.colA),0) AS BHasMatchesForA
FROM
	TableA a
	FULL JOIN TableA b ON a.colA = b.colB;	
You could use dynamic SQL with ms_sp_MSforeachtable to go through all combinations of tables.
Go to Top of Page

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 10/04/2013 :  18:50:42  Show Profile  Reply with Quote
I appreciate that James, me being a purely Production guy I admit I don't fully grasp the whole programming logic, nor have I yet tested the statement, but shouldn't the full join clause nominate TableB as b, not TableA as b ?

Also, if you don't mind, let me test my understanding (I'm in the throes of a long, LONG overdue improvement of my TSQL)...

The statement joins all values on both columns in question, gets their rowcounts then derives a percentage value..but the division has me flummoxed!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000