Solved

SQL : Using WildChar ON - INNER JOIN QUERY

Posted on 2012-04-10
5
423 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

863 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

18 Experts available now in Live!

Get 1:1 Help Now