June 5, 2017 at 9:39 pm
Hi Experts,
I am trying to write a query to join two table but so far no success with joining the second column to check if its a match or not.
Here is a sample table and result I ve setup.
DECLARE @a TABLE( A_Path VARCHAR(MAX), A_Filename Varchar(50) , A_No Varchar(50))
INSERT @a
SELECT '123' Path, 'Dog' Filename ,'1' No UNION ALL
SELECT '456', ' Cat', '2' UNION ALL
SELECT '789', ' Dog', '3' UNION ALL
SELECT '987', ' Cat', '4' UNION ALL
SELECT '876', ' Dog', '5' UNION ALL
SELECT '531', ' Cat', '6' UNION ALL
SELECT '999', ' Cat' ,'7'
SELECT * FROM @a
DECLARE @b-2 TABLE( B_Path VARCHAR(MAX), B_Filename Varchar(50) , B_No Varchar(50))
INSERT @b-2
SELECT '123A3' Path, 'Dog' Filename ,'11' No UNION ALL
SELECT '45643', ' Cat', '12' UNION ALL
SELECT '78998', ' Dog', '13' UNION ALL
SELECT '98712', ' Cat', '14' UNION ALL
SELECT '876666', ' Dog', '15' UNION ALL
SELECT '5311242', ' Dog', '16'
SELECT * FROM @b-2
DECLARE @RESULT TABLE( A_Path VARCHAR(MAX), A_Filename Varchar(50) , A_No Varchar(50),B_PATH Varchar(50),B_Filename_MATCH Varchar(50),B_NO Varchar(50))
INSERT @RESULT
SELECT '123' A_Path, 'Dog' A_Filename ,'1' A_No , '123A3' B_PATH , 'YES' B_Filename_MATCH, '11' B_NO UNION ALL
SELECT '456', ' Cat', '2' ,'45643','YES','12' UNION ALL
SELECT '789', ' Dog', '3','78998', ' YES', '13' UNION ALL
SELECT '987', ' Cat', '4' ,'98712', ' YES', '14' UNION ALL
SELECT '876', ' Dog', '5','876666', ' YES', '15' UNION ALL
SELECT '531', ' Cat', '6' ,'5311242', ' NO', '16' UNION ALL
SELECT '999', ' Cat' ,'7' , 'NO_MATCH','NULL','NULL'
SELECT * FROM @RESULT
1. In the results column I am trying to get all Entries of A.
2. B_Path has Entries for A (I am doing this with Wildcard Char with limited success.)
Main Problem.
3. If the entry matches ,Also check if Filename Matches. (Not able to do this w/o loosing entries from A)
4 If there is Match for A_ Path Show NULL.
Also,
Is there a way to check the quality of match ?
For example 123 and 123678 Vs 1234 vs 12345 Vs 123 Vs 1234 (I dont know how to better phrase this technically) ?
Thanks and Regards
Ravi T
June 5, 2017 at 11:54 pm
Quick solution
😎
DECLARE @a TABLE( A_Path VARCHAR(MAX), A_Filename Varchar(50) , A_No Varchar(50))
INSERT @a
SELECT '123' Path, 'Dog' Filename ,'1' No UNION ALL
SELECT '456', ' Cat', '2' UNION ALL
SELECT '789', ' Dog', '3' UNION ALL
SELECT '987', ' Cat', '4' UNION ALL
SELECT '876', ' Dog', '5' UNION ALL
SELECT '531', ' Cat', '6' UNION ALL
SELECT '999', ' Cat' ,'7'
DECLARE @b-2 TABLE( B_Path VARCHAR(MAX), B_Filename Varchar(50) , B_No Varchar(50))
INSERT @b-2
SELECT '123A3' Path, 'Dog' Filename ,'11' No UNION ALL
SELECT '45643', ' Cat', '12' UNION ALL
SELECT '78998', ' Dog', '13' UNION ALL
SELECT '98712', ' Cat', '14' UNION ALL
SELECT '876666', ' Dog', '15' UNION ALL
SELECT '5311242', ' Dog', '16'
SELECT
A.A_Path
,A.A_Filename
,A.A_No
,ISNULL(B.B_Path,'NO_MATCH') AS B_Path
,CASE
WHEN A.A_Filename = B.B_Filename THEN 'YES'
WHEN A.A_Filename <> B.B_Filename THEN 'NO'
END AS B_Filename_MATCH
,B.B_No
,(0.0 + LEN(A.A_Path)) / (0.0 + LEN(B.B_Path)) AS Match_Ratio
FROM @a A
LEFT OUTER JOIN @b-2 B
ON A.A_Path = SUBSTRING(B.B_Path,1,LEN(A.A_Path));
June 6, 2017 at 8:44 am
Erikur has a quick solution, but there isn't a easy way to determine match quality or partial matching of different lengths.
How do you decide when 123 matches 1234 or 12345? Or does it match both? Are you matching the complete value from the a side against anything close on the b?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply