• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 49
  • Last Modified:

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?
0
ghettocounselor
Asked:
ghettocounselor
  • 2
  • 2
2 Solutions
 
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
 
ghettocounselorPharmacy Systems AdminAuthor 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
 
ghettocounselorPharmacy Systems AdminAuthor 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now