SELECT a.Product_ID, a.Product_Code, a.Product_Name, a.Caption, a.SpecsList, a.ImagePath, a.PreOrder, b.Dept_ID, b.Dept_Name, b.Dept_Alt, b.AltUrL, c.Brand_ID, c.Brand, d.Map, d.MSRP, d.Sale FROM Product a INNER JOIN Departments b ON a.Dept_ID = b.Dept_ID INNER JOIN Brand c ON a.Brand_ID = c.Brand_ID INNER JOIN Price d ON a.Product_ID = d.Product_ID INNER JOIN Website e ON d.Site_ID = e.Site_ID WHERE (a.Dept_ID = 50 OR a.Product_ID IN (SELECT DISTINCT Product_ID FROM Product_Departments WHERE Product_Departments.Dept_ID = 50)) AND a.Deleted=0 AND a.Orderable = 0
