Solved

SQL : Using WildChar ON - INNER JOIN QUERY

Posted on 2012-04-10
5
421 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
Comment Utility
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
Comment Utility
@nishant i thought this is complicated, but it went easy !! Thank you !!
0
 
LVL 3

Expert Comment

by:Chris__W
Comment Utility
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
Comment Utility
@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
Comment Utility
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Full Text Search string 5 32
Sql query for filter 12 21
Sql query to Stored Procedure 6 13
Test a query 23 11
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now