with skuAttributes as ( SELECT -1 as attrGrpID, 'Uncategorized' as attrGrp, -1 as attrID, 'Uncategorized' as attrItem union all SELECT attrGrp.attrID AS attrGrpID, attrGrp.attrDescription AS attrGrp, attrItem.attrID, attrItem.attrDescription AS attrItem FROM tbl_PRD_Attributes attrGrp with (nolock) inner join tbl_PRD_Attributes attrItem with (nolock) on attrItem.OwnerID = attrGrp.OwnerID AND attrItem.FSL_TableCode = attrGrp.FSL_TableCode inner join tbl_PRD_AttributeLinks link with (nolock) on link.mstr_attrid = attrGrp.attrID AND link.mstr_attrTypeID = 3 AND link.child_attrTypeID = 4 AND link.child_attrID = attrItem.attrid inner join tbl_PRD_AttributeLinks link2 with (nolock) on link2.child_attrid = attrItem.attrid AND link2.child_attrTypeid = 4 AND link2.mstr_attrTypeID = 3 WHERE attrGrp.OwnerID = AND attrGrp.FSL_TableCode = AND attrGrp.attrTypeID = 3 ) #forJSON( 'skuAttributes', attributes.dataStore, 'attrItem' )#