Women's Medical Clogs

SELECT p.* FROM dev1.ds_products as p, ds_product_categories as c, ds_product_option_sets as d where (p.invLevel > 0 or (p.invTrack = 'O' and d.inventory > 0)) and p.pID = d.productId and p.pID = c.pID and c.catID !=0 and p.pAvail = 'Y' and ( p.venID = '2' ) and p.pID in (0,107,110,112,113,114,115,118,119,120,122,123,127,130,132,134,200,248,249,250,251,252,253,254,256,283,288,289,290,291,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,545,546,547,548,549,550,551,552,553,554,555,560,561,562,563,564,565,566,567,568,569,570,585,586,587,588,589,590,591,592,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,653,654,655,656,657,658,659,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,682,683) group by p.pID order by case c.sortOrder when 0 then 100 else c.sortOrder end