Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL syntax for like using another field in another table

Posted on 2015-02-16
6
Medium Priority
?
112 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 12

Expert Comment

by:Nathan Riley
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 12

Expert Comment

by:Nathan Riley
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
AWS Certified Solutions Architect - Associate

This course has been developed to provide you with the requisite knowledge to not only pass the AWS CSA certification exam but also gain the hands-on experience required to become a qualified AWS Solutions architect working in a real-world environment.

 
LVL 23

Accepted Solution

by:
Michael Fowler earned 2000 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

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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…
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.

715 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