Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 469
  • Last Modified:

SQL : Using WildChar ON - INNER JOIN QUERY

I have a query which results values as

Id  drugName

1     TRUVADA 200 MG TABLET            
2     Norvir   100 MG  TABLET
3     LEXIVA 700 MG TABLET      
4     VERAPAMIL 180MG TABLET SA  
5     LEXIVA 200 MG TABLET      
6     Norvir   500 MG  TABLET


What i need to do is, I need to compare with a Table Name : Drugs which has values as

Id DrugName

1     TRUVADA              
2     LEXIVA
3     Zoloft
4     complera

On comparing with my query results and DrugTable, i need to return only matching values. in the query result and i have to eliminate remaining records from the query results.

In the above scenario

Output should be ..

Id       DrugName

1     TRUVADA 200 MG TABLET            
2     LEXIVA 700 MG TABLET      
3     LEXIVA 200 MG TABLET    


I need some syntax help to write something similar to this ..

SELECT from QUERY QRY
INNER JOIN DRUGS DRG
on 'QRY.DRUGNAME%' = 'DRG.DRUGNAME%'
0
chokka
Asked:
chokka
  • 2
  • 2
1 Solution
 
nishant joshiTechnology Development ConsultantCommented:
let say 1st table
FullTable

Id  drugName

1     TRUVADA 200 MG TABLET            
2     Norvir   100 MG  TABLET
3     LEXIVA 700 MG TABLET      
4     VERAPAMIL 180MG TABLET SA  
5     LEXIVA 200 MG TABLET      
6     Norvir   500 MG  TABLET

And 2nd as
Partialtable

1     TRUVADA 200 MG TABLET            
2     LEXIVA 700 MG TABLET      
3     LEXIVA 200 MG TABLET    

Then your query should be

Select  * from FullTable F
join PartialTable p
on F.drugName like p.drugName+'%'

Open in new window


Have a great day...
0
 
chokkaStudentAuthor Commented:
@nishant i thought this is complicated, but it went easy !! Thank you !!
0
 
Chris__WCommented:
Hello,

I think nishantcomp2512 has the right idea, but the tables involved would be different:

SELECT * INTO OutputTbl
FROM dbo.QueryOut qry INNER JOIN dbo.DrugName drg
ON qry.DrugName LIKE (drg.DrugName + '%')

The "partialtable" is what he's looking for as his output table, not an input table.

Also, you can try replacing the ON line with:
WHERE CONTAINS (qry.DrugName, drg.DrugName)

Thanks,
Chris
0
 
chokkaStudentAuthor Commented:
@chris, you think your query and @nishant query differs in output ??

I'm willing to give a try.
0
 
Chris__WCommented:
My comment was just clarifying the solution nishant put in to use the tables you listed as imput.  The WHERE CONTAINS is a different method of comparing text, but theoretically the output should not differ.  If you have trouble using one, try the other.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now