Solved

SQL like query not matching up items between tables in join

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

3 Use Cases for Connected Systems

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

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
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.…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

776 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