Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL : Using WildChar ON - INNER JOIN QUERY

Posted on 2012-04-10
5
Medium Priority
?
457 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 2000 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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

580 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