chokka
asked on
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%'
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%'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
@chris, you think your query and @nishant query differs in output ??
I'm willing to give a try.
I'm willing to give a try.
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.
ASKER