Search code examples
sqljoinsql-like

Search with 'LIKE' function within an inner join

I know the following statement is invalid:

SELECT Terms.LName, Terms.FName, AS400.SystemID, AS400.userid, AS400.Status, AS400.text
FROM Terms 
    INNER JOIN AS400 ON WHERE text LIKE %Terms.LNAME = AS400.text;

The AS400.text field is a free text field on the AS400 that we use to store users first and last name.

What I would like to do is search the AS400.text field for the contents of the Terms.LName field.

I did create a dummy AS400.LName column to test my inner join, and this works fine:

SELECT Terms.LName, Terms.FName, AS400.SystemID, AS400.userid, AS400.Status, AS400.text
FROM Terms 
    INNER JOIN AS400 ON Terms.LName = AS400.LName;

Ideas?

Solution

  • I think you are looking for something like:

    SELECT 
 Terms.LName,
 Terms.FName, 
 AS400.SystemID, 
 AS400.userid, 
 AS400.Status, 
 AS400.text
FROM 
 Terms 
 INNER JOIN AS400 ON 
   AS400.text LIKE '%' + Terms.LNAME + '%'