SQL syntax for like using another field in another table

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 ???
ghettocounselorPharmacy Systems AdminAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Michael FowlerConnect With a Mentor Solutions ConsultantCommented:
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
 
Nathan RileyFounder/CTOCommented:
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
 
ghettocounselorPharmacy Systems AdminAuthor Commented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Nathan RileyFounder/CTOCommented:
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
 
ghettocounselorPharmacy Systems AdminAuthor Commented:
Looks good, thanks!
0
 
ghettocounselorPharmacy Systems AdminAuthor Commented:
Still having a bit of trouble on this one after digging into it.

query example
0
All Courses

From novice to tech pro — start learning today.