i need a query to set Products_Extended.METATAG_
Title to be like this....
[Product Name] is made by [ProductManufacturer] and is listed in our [Category] and [Subcategory]
where ProductName is in the Products table and ProductManufacturer and METATAG_Title are in the Products_Extended table there is a ProductID field in each table that has equivalent values when the product is the same. and the is a table Categories_Products_Link. that has fields ID (which coresponds to the ProductID) and CategoryID which has an entry for each category that a product belongs to (a product may have more than one category). The Categories table has CategoryName and ParentID of the parent Category. See the query bellow to get an idea - i think there's probably some syntax mistakes
I'm running this query on Volusion which is a shopping cart system so i can't see the sql errors. I ran the query bellow which has some error because it didn't work. For now its ok even though a product may have many categories we can just take any one an use it
UPDATE Products_Extended LEFT JOIN Products p ON Products_Extended.ProductID=p.ProductID LEFT JOIN (SELECT Categories_Products_Link.*, Categories.CategoryName FROM (Categories_Products_Link,Categories) WHERE Categories.CategoryID=Categories_Products_Link.CategoryID) AS cp ON cp.ID=Products_Extended.ProductID LEFT JOIN Categories ON cp.ParentID=Categories.CategoryID SET Products_Extended.METATAG_Title = p.ProductName+' is made by ' + Products_Extended.ProductManufacturer + ' and is listed in our ' +Categories.CategoryName + ' and ' + cp.CategoryName