Solved

SQL syntax for like using another field in another table

Posted on 2015-02-16
6
108 Views
Last Modified: 2015-02-17
I have two tables.
ChemoDrugList
DrugList
and the only field common between the two will not match precisely, although they have the same naming convention.
Example, where ChemoDrugList might have a drugname like AFINITOR 10MG TAB the DrugList table would have AFINITOR or AFINITOR CHLORIDE. One convention that might be useful is that up the 1st space in either table would be a good place to stop the comparison.

What I need to do is somehow do something that might look like this
SELECT * FROM ChemoDrugList, DrugList
where ??? something here where I can say compare each field up to the space ???
0
Comment
Question by:ghettocounselor
  • 3
  • 2
6 Comments
 
LVL 11

Expert Comment

by:N R
ID: 40612922
What field do you join on in both?

Something like this would do it.

select *
from ChemoDrugList cdl, DrugList dl
where cdl.fieldname like '%afinitor%'
and dl.fieldname like '%afinitor%'

Open in new window

0
 

Author Comment

by:ghettocounselor
ID: 40612930
drugName is field name

so how do I deal with the fact that the name in the field will one minute be afinitor and the next lidocaine, there are about 5000 records in each table.
0
 
LVL 11

Expert Comment

by:N R
ID: 40612936
I'm not sure what you mean? You didn't say that they fields are changing in the question above, just that they have similar names, but not exact matches.
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 23

Accepted Solution

by:
Michael74 earned 500 total points
ID: 40612940
Use the LIKE predicate
http://www.w3schools.com/sql/sql_like.asp

SELECT *
FROM ChemoDrugList CDL INNER JOIN DrugList DL
ON CDL.drugname LIKE DL.drugname + '%'
Where CDL.drugname = 'lidocaine'
0
 

Author Closing Comment

by:ghettocounselor
ID: 40612969
Looks good, thanks!
0
 

Author Comment

by:ghettocounselor
ID: 40615233
Still having a bit of trouble on this one after digging into it.

query example
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

730 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