Solved

SQL like query not matching up items between tables in join

Posted on 2015-02-19
3
55 Views
Last Modified: 2015-02-19
Hi, In the image I'm expecting that the two fields would match up based on the use of the % in the syntax, clearly I'm misunderstanding this :)

like-query-Feb2015.png
0
Comment
Question by:ghettocounselor
  • 2
3 Comments
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 40619881
Correct ;)

Your condition P.BrandName LIKE P2.DrugName + '%' is looking for any product which starts with "ALKERAN 2MG TAB"

Using P2.DrugName  LIKE P.BrandName + '%' would look for drugs starting with e.g. "ALKERAN". So this would give you results for DRUG_CODE 1176 in table PDM. But none for 1171.

But just to clarify this: What is your actual goal here? Cause your question makes my problem-awareness senses tingeling. It seems that you should consider to tokenize your drug and brand names and doing a token matching. Using stop words to filter out tokens like 2MG or TAB.
0
 

Author Comment

by:ghettocounselor
ID: 40620056
Switching the names solved the matching problem. Thanks, makes sense when you see it.

Regards you problem-awareness you are correct there are some potential issues here but due to the volume of items in P2 (only 94) I'm in good shape with this currently working as it does, in the end the matchup with P (5400 items) isn't perfect, some items in P2 are not in P and some of the P2's matched multiple items in P (this is what your tingling was). However the lack of specificity in this case is, at least with my DB skills, managed faster in this one time event (never to be done again) simply scanning the 77 matches visually for inappropriate matches.

Don't get me wrong I'm intrigued by the idea of solving this fully in SQL :) if you are interested in the same.

Here's the trick with this; example below of the fields that might match up across both tables. DRUG_NAME is from P2 all others from P.
 QUERY
SELECT DISTINCT P2.DRUG_NAME, P.DRUG_CODE, P.GENERIC_NAME, P.BRAND_NAME, P.STRENGTH, P.STRENGTH_UNIT, P.VOLUME_UNIT FROM PDM P INNER JOIN SLHS_RX_CHEMO_LIST P2
	ON P2.DRUG_NAME LIKE P.BRAND_NAME + '%'
	AND P.[ROUTE] LIKE 'PO'

Open in new window

0
 

Author Closing Comment

by:ghettocounselor
ID: 40620058
ste5an answered the question and posed another valid point but since my initial question was answered I've awarded the point.
Thanks!
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question