SQL query from 2 table into one (is this done by using join?)

I have a table called products, products has several fields, 2 of those are of importance in this question, column ID and column ProdDetail.

I have another table lets call it table2. Table2 has an ID column and a Name column.

I want to run an sql query which does the following:

insert into table3  products.ID and table2.ID 
where products.ProdDetail CONTAINS 开发者_Python百科table2.Name

I know that this code isn't valid sql statement. Also I can't use join because products.ProdDetail contains more data than just table2.name, so they are not exactly the same.

You mean something like this?

INSERT  INTO table3 ( ProductID, Table2ID )
        SELECT  product.Id,
                table2.ID
        FROM    products
                INNER JOIN table2 ON products.ProdDetail LIKE CONCAT('%', table2.name, '%')

INSERT INTO table3 (id, name)
SELECT
  p.id as ID
  , p.productDetail as name
FROM products p 
INNER JOIN table2 t2 ON (t2.id = p.id) 
      AND (p.productdetail LIKE CONCAT('%',t2.name,'%') 

INSERT INTO
  table3
SELECT
  products.ID,
  table2.ID
FROM
  products
INNER JOIN
  table2
    ON products.ProdDetail LIKE '%' + table2.Name + '%'

I think that this is what you want:

INSERT INTO Table3 (prod_id, table2_id)
SELECT
    P.id,
    T2.id
FROM
    Products P
INNER JOIN Table2 T2 ON
    P.ProdDetail LIKE '%' + T2.name + '%'

Just keep in mind that if you have any table 2 names that are substrings of another name that you're going to get duplicate/erroneous data. For example, if you have Table2 names of "widgets" and "foobar widgets" and you have a Product description of "These are foobar widgets" then it's going to relate both Table2 rows to the Product row.