Solved

SQL like query not matching up items between tables in join

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

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying 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

Suggested Solutions

Title # Comments Views Activity
Updating a field based from a if exist.... 2 44
SQL Server syntax 11 49
SP converting date time to date and time separately 2 38
Display SQL maintenance plan SQL Code 3 47
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

732 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