Solved

SQL like query not matching up items between tables in join

Posted on 2015-02-19
3
44 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 32

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

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.…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now