New quick help correcting SQL Statement 1

[grippy]

INSERT INTO Product_Category_Mapping
(ProductId, CategoryId)
SELECT Product.Id AS ProductId, Category.Id AS CategoryId
FROM Product, Category 
WHERE Product.Name LIKE Category.Name

Hello,

I keep coming up with 0 results and can not figure this out. I am trying to find Category.Name in Product.Name and insert the Category.Id along with the Product.Id into the table Product_Category_Mapping. For example the Category.Name could be Chevrolet Cavalier and the Product.Name could be Brake Rotor for 00-05 Chevrolet Cavalier 2.4L. Please let me know if you can help.

Thank you!

 

[bborissov]

INSERT INTO Product_Category_Mapping
(ProductId, CategoryId)
SELECT Product.Id  AS ProductId
      ,Category.Id AS CategoryId
FROM Product
INNER JOIN Category  ON Product.Name LIKE '%' + Category.Name +'%'

(I just like JOINs more than old syntax )
If Category.Name is with fixed length(CHAR() or NCHAR()) you should TRIM() it to get the results.

BTW you can always test the SELECT statement first and the use it in INSERT.

Borislav Borissov
VFP9 SP2, SQL Server

 

[grippy]

Awesome this works great however I found one slight issue. I have categories called BMW M, BMW M3, BMW M5 and the code finds the BMW M in all of them and creates duplicates. Do you have any idea how to correct that one? technically its working and finding the match but just ignoring the 3 or the 5 from the M3 and M5 and putting them into the BMW M category as well as the M3 and M5. Let me know if you have any ideas? Thanks a bunch for your help this saved me several hours of frustration.

 

[bborissov]

....
INNER JOIN Category  ON ' '+Product.Name+' ' LIKE '% ' + Category.Name +' %'

Borislav Borissov
VFP9 SP2, SQL Server

 

[grippy]

Worked perfectly! Thank you so much!