JOIN not exact variables together - SQL

Hi have two tables of drug listings and I need to join them together on their names (GENERIC_NAME and DRUG_NAME) and the only issue is that the naming is not precise.

For instance what may have a DRUG_NAME of "Brentuximab Vedotin" might have a GENERIC_NAME of "Brentuximab" and vice versa. Despite these differences I do want them to match. If 'how much' matching is a question then I'd say at the very least the first word should match, if it can be smarter than that I'd say the first 6 characters.

SELECT P.GENERIC_NAME, HD.DRUG_NAME FROM StagingPharmacy.DBO.PDM P RIGHT JOIN HAZARDOUS_DRUGS HD
	ON P.GENERIC_NAME = HD.DRUG_NAME

Open in new window


join-match-not-exact
How can I accommodate for this?
ghettocounselorDirector Pharmacy InformaticsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lokesh B RDeveloperCommented:
Hi,

Try this query which will compare first 6 characters.

SELECT P.GENERIC_NAME, HD.DRUG_NAME FROM StagingPharmacy.DBO.PDM P RIGHT JOIN HAZARDOUS_DRUGS HD       ON. SUBSTRING( P.GENERIC_NAME, 0, 6) = SUBSTRING(HD.DRUG_NAME, 0, 6)
0
BeartlaoiCommented:
Create a translate table in which you can put your proper equivalents, this way you wont have problems with two things matching that really arent the same.
Then use a union query to merge both kinds of results together.

Three pieces here UNION together, first is your original query to get exact name matches
Second is translating in one direction
Third is translating in other direction
SELECT P.GENERIC_NAME, HD.DRUG_NAME FROM StagingPharmacy.DBO.PDM P RIGHT JOIN HAZARDOUS_DRUGS HD
	ON P.GENERIC_NAME = HD.DRUG_NAME
UNION
SELECT P.GENERIC_NAME, HD.DRUG_NAME FROM StagingPharmacy.DBO.PDM P 
        RIGHT JOIN TranslateTable TT ON P.GENERIC_NAME = TT.Name1 
        RIGHT JOIN HAZARDOUS_DRUGS HD ON TT.Name2 = HD.DRUG_NAME
UNION
SELECT P.GENERIC_NAME, HD.DRUG_NAME FROM StagingPharmacy.DBO.PDM P 
        RIGHT JOIN TranslateTable TT ON P.GENERIC_NAME = TT.Name2 
        RIGHT JOIN HAZARDOUS_DRUGS HD ON TT.Name1 = HD.DRUG_NAME

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ghettocounselorDirector Pharmacy InformaticsAuthor Commented:
Hi Beartlaoi the post from Lokesh B R seems to do the trick. Is there something about that offering that is not kosher in your mind? Also with your post am I to first create the TranslateTable  then insert the results of this query into that table? Thanks, Ghetto
0
BeartlaoiCommented:
I am not sure if the first 6 letters are sufficiently unique. HydroCortizone != HydroCodone
And yes, you would need to create the translate table first, fillit in with the ones you know you have problems with.  You can add new translations later as needed.
0
ghettocounselorDirector Pharmacy InformaticsAuthor Commented:
Hi Beartlaoi, yes I see. Fortunately the listing of HAZARDOUS_DRUGS is not an extensive list and the names tend to be rather unique. Additionally there will be some human intervention in the next step of the process, I'm really only looking to get the grunt work done in SQL, picking out a couple of misinterpretations shouldn't pose a problem. More to come. And Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.