July 30, 2014 at 8:20 am
I have three tables I want to match two of them based on the third.
CREATE TABLE #Check1 (IDX INT, C1FirstName VARCHAR(50));
INSERT INTO #Check1 (IDX, C1FirstName) VALUES (1, 'ELIZABETH');
CREATE TABLE #Check2 (IDX INT, C2FirstName VARCHAR(50));
INSERT INTO #Check2 (IDX, C2FirstName) VALUES (1, 'BETTY J'); -- Notice the middle initial
CREATE TABLE #Compare (Name1 VARCHAR(50), Name2 VARCHAR(50));
INSERT INTO #Compare (Name1, Name2) VALUES
('Betty', 'Bess'),
('Betty', 'Beth'),
('Betty', 'Eli'),
('Betty', 'Eliza'),
('Betty', 'Libby'),
('Betty', 'Liz'),
('Betty', 'Liza'),
('Betty', 'Lizzie'),
('Betty', 'Lizzy'),
('Betty', 'Elizabeth');
SELECT DISTINCT C1.IDX
, C2.C2FirstName
, C1.C1FirstName
, CASE WHEN CHARINDEX(' ',C2.C2FirstName) > 0 THEN PARSENAME(REPLACE(C2.C2FirstName, ' ', '.'), 2) ELSE C2.C2FirstName END AS NewFName
, CASE WHEN CHARINDEX(' ',C2.C2FirstName) > 0 THEN PARSENAME(REPLACE(C2.C2FirstName, ' ', '.'), 1) ELSE NULL END AS NewMName
, CASE
WHEN (CASE WHEN CHARINDEX(' ',C2.C2FirstName) > 0 THEN PARSENAME(REPLACE(C2.C2FirstName, ' ', '.'), 2) ELSE C2.C2FirstName END) = C1.C1FirstName THEN 1
WHEN (CASE WHEN CHARINDEX(' ',C2.C2FirstName) > 0 THEN PARSENAME(REPLACE(C2.C2FirstName, ' ', '.'), 2) ELSE C2.C2FirstName END) = M1.Name1 AND C1.C1FirstName = M1.Name2 THEN 2
ELSE 0
END AS Checkit
, M1.Name1
, m1.Name2
FROM #Check2 C2
INNER JOIN #Check1 C1 ON C2.IDX = C1.IDX
-- Left join as data will not always match table #Campare
LEFT JOIN #Compare M1 ON CASE WHEN CHARINDEX(' ',C2.C2FirstName) > 0 THEN PARSENAME(REPLACE(C2.C2FirstName, ' ', '.'), 2) ELSE C2.C2FirstName END = M1.Name1
;
This gives me ten records, one for each record in the table #Campare. All I want is the record where Name1 = 'Betty' and Name2 = 'Elisabeth'.
I know it is a join problem but I do not see it. :crying:
So where do I look to correct the problem?
Hope I have made myself clear.
Thanks
July 30, 2014 at 8:49 am
I did come up with this:
SELECT DISTINCT C1.IDX
, C2.C2FirstName
, C1.C1FirstName
, CASE WHEN CHARINDEX(' ',C2.C2FirstName) > 0 THEN PARSENAME(REPLACE(C2.C2FirstName, ' ', '.'), 2) ELSE C2.C2FirstName END AS NewFName
, CASE WHEN CHARINDEX(' ',C2.C2FirstName) > 0 THEN PARSENAME(REPLACE(C2.C2FirstName, ' ', '.'), 1) ELSE NULL END AS NewMName
, CASE
WHEN (CASE WHEN CHARINDEX(' ',C2.C2FirstName) > 0 THEN PARSENAME(REPLACE(C2.C2FirstName, ' ', '.'), 2) ELSE C2.C2FirstName END) = C1.C1FirstName THEN 1
WHEN (CASE WHEN CHARINDEX(' ',C2.C2FirstName) > 0 THEN PARSENAME(REPLACE(C2.C2FirstName, ' ', '.'), 2) ELSE C2.C2FirstName END) = M1.Name1 AND C1.C1FirstName = M1.Name2 THEN 2
WHEN M1.Name1 = M2.Name1 THEN 3
ELSE 0
END AS Checkit
, M1.Name1
, m1.Name2
FROM #Check2 C2
INNER JOIN #Check1 C1 ON C2.IDX = C1.IDX
LEFT JOIN #Compare M1 ON (CASE WHEN CHARINDEX(' ',C2.C2FirstName) > 0 THEN PARSENAME(REPLACE(C2.C2FirstName, ' ', '.'), 2) ELSE C2.C2FirstName END) = M1.Name2
LEFT JOIN #Compare M2 ON C1.C1FirstName = M2.Name2
;
which gives me just the desired result.
Does that seem okay?
July 30, 2014 at 8:55 am
SELECT DISTINCT C1.IDX
, C2.C2FirstName
, C1.C1FirstName
, CASE WHEN CHARINDEX(' ',C2.C2FirstName) > 0 THEN PARSENAME(REPLACE(C2.C2FirstName, ' ', '.'), 2) ELSE C2.C2FirstName END AS NewFName
, CASE WHEN CHARINDEX(' ',C2.C2FirstName) > 0 THEN PARSENAME(REPLACE(C2.C2FirstName, ' ', '.'), 1) ELSE NULL END AS NewMName
, CASE
WHEN (CASE WHEN CHARINDEX(' ',C2.C2FirstName) > 0 THEN PARSENAME(REPLACE(C2.C2FirstName, ' ', '.'), 2) ELSE C2.C2FirstName END) = C1.C1FirstName THEN 1
WHEN (CASE WHEN CHARINDEX(' ',C2.C2FirstName) > 0 THEN PARSENAME(REPLACE(C2.C2FirstName, ' ', '.'), 2) ELSE C2.C2FirstName END) = M1.Name1 AND C1.C1FirstName = M1.Name2 THEN 2
ELSE 0
END AS Checkit
, M1.Name1
, m1.Name2
FROM #Check2 C2
INNER JOIN #Check1 C1 ON C2.IDX = C1.IDX
-- Left join as data will not always match table #Campare
LEFT JOIN #Compare M1 ON CASE WHEN CHARINDEX(' ',C2.C2FirstName) > 0 THEN PARSENAME(REPLACE(C2.C2FirstName, ' ', '.'), 2) ELSE C2.C2FirstName END = M1.Name1
and c1.C1FirstName = m1.Name2
;Looks like you just missed the second join criteria (C1 Firstname = M1 Name 2). I would also think about recording first and middle name separately in Check2.
July 30, 2014 at 9:03 am
Keith Tate (7/30/2014)
Looks like you just missed the second join criteria (C1 Firstname = M1 Name 2). I would also think about recording first and middle name separately in Check2.
Thank you for the reply. I actually through the bit with the middle initial in as a side problem. :blush:
Client sent date with first name and middle name in the same column even though they had a middle name column.
I have not yet create a temporary table for the split or gotten permission to split in the permanent table. Working on it.
Thanks again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply