Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL like query not matching up items between tables in join

Posted on 2015-02-19
3
Medium Priority
?
68 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 35

Accepted Solution

by:
ste5an earned 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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.…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

636 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