sultan_tipu
New Member
I am trying to build a SQL statement which pulls records from a table but sorts them by another table.Main - is my Main Products TableMainCategory - is the Categorys of productsEach product can be in multiple categories - so in Main I have a field called CategoryIds which containts the ID's of all the categories that product belongs to. (I know now that is the wrong way to do it)Tables Layout:
So basically when I click on a Category, e.g. Brands I want to be able to sort the products in each category to display in the order I want - so thats why I have created the table CatSortI can put the CategoryId in there with the ProductId and DisOrder is the way they are displayed.So on my page the SQL statement:\[code\]Select * From Main Where CategoryIds = '11' OR CategoryIds LIKE '11,%' OR CategoryIds LIKE '%, 11,%' OR CategoryIds LIKE '%, 11'\[/code\]pulls all the products in the Category 'Brand' but how do I extend that SQL statement to sort them by CatSort, bearing in mind that not all the records in that Category will be in CatSort (or would it be better if they were? they only get added to CatSort when they actually sort them to order they prefer. But if a new product is added to that Category, it doesn't automatically get added to CatSort.)