SELECT C.description AS catalogue_name, CG.description AS group_name, CC.description AS category_name, SC.description AS subcategory_name, P.code AS product_code, P.product_name, P.description AS product_description, P.productID, P.product_name AS Expr1, P.description, P.blurb, P.new_product, P.focus, P.retail_price, P.special_price, P.special_start, P.special_end, I.file_name FROM dbo.catalogue_group AS CG WITH (NOLOCK) RIGHT OUTER JOIN dbo.catalogue_products AS P WITH (NOLOCK) ON CG.groupID = P.groupID LEFT OUTER JOIN dbo.catalogue_category AS CC WITH (NOLOCK) ON P.categoryID = CC.categoryID AND CG.groupID = CC.groupID LEFT OUTER JOIN dbo.catalogue_subcategory AS SC WITH (NOLOCK) ON P.subcategoryID = SC.subcategoryID FULL OUTER JOIN dbo.catalogue_product_images AS I WITH (NOLOCK) ON I.productID = P.productID FULL OUTER JOIN dbo.catalogue AS C WITH (NOLOCK) ON CG.catalogueID = C.catalogueID WHERE P.visible = 1 AND CG.anonymous_view=1 AND I.primary_image = 1 AND P.groupID = 7 ORDER BY CG.sequence, SC.sequence, CC.sequence, P.sequence