Select items like records from a column in another table

Question

I have a table (foo) with a large number of several records and I only want to select those that are like one of the records in a field in another table (foo2)

If I do a SELECT query with an inner join

SELECT pst_qty AS [QTY]
  ,[MFGPN]
  ,[mfg_name] AS [MANUFACTURER]
  ,description
  ,sup_id
  FROM [foo]
  INNER JOIN [foo2]
  ON [foo].[MFGPN] = [foo2].TestString

afaik I would only get records where foo.field1=foo2.field1. I can't seem to use ON foo.field1 LIKE foo2.field2 to select records like the fields in foo2. How would I go about selecting the records that are like the records from a column in a different table?

Answer 1

Have you tried something like

SELECT pst_qty AS [QTY] 
  ,[MFGPN] 
  ,[mfg_name] AS [MANUFACTURER] 
  ,description 
  ,sup_id 
  FROM [foo] 
  INNER JOIN [foo2] 
  ON [foo].[MFGPN] LIKE '%' + [foo2].TestString + '%'

Answer 2

how about,

SELECT DISTINCT
            F.[pst_qty] [QTY],
            F.[MFGPN], 
            F.[mfg_name] [MANUFACTURER], 
            F.[description], 
            F.[sup_id] 
    FROM
            [foo] F 
        CROSS JOIN
            [foo2] F2
    WHERE
             CHARINDEX(F2.[MFGPN], F.[TestString]) > 0;

Note, this will work if the strings contain the the '%' wildcard character.