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
Solved

SQL : Using WildChar ON - INNER JOIN QUERY

Posted on 2012-04-10
5
428 Views
Last Modified: 2012-04-10
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
Comment
Question by:chokka
  • 2
  • 2
5 Comments
 
LVL 14

Accepted Solution

by:
nishant joshi earned 500 total points
ID: 37829627
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
 

Author Comment

by:chokka
ID: 37829647
@nishant i thought this is complicated, but it went easy !! Thank you !!
0
 
LVL 3

Expert Comment

by:Chris__W
ID: 37829730
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
 

Author Comment

by:chokka
ID: 37829765
@chris, you think your query and @nishant query differs in output ??

I'm willing to give a try.
0
 
LVL 3

Expert Comment

by:Chris__W
ID: 37829789
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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

838 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