sqlAttributes: select distinct avm.AttributeMappingID,AttributeName,av.AttributeNameId,AttributeValue, type,cssProperty, isnull(avm2.linkedto, isnull(avm22.linkedto2,0)) isLink, coalesce(spg.GroupName, spg1.GroupName) SubProductGroupName, coalesce(coalesce(spg.isMainGroup,spg1.isMainGroup),0) isMainGroup, av.AttributeValueId, attributeValueName, avm.isDefault, avm.Price, controlType, attributeGroup, Explanation, isnull(avm.linkedTo,0) linkedTo, isnull(avm.linkedto2,0) linkedTo2, an.sortOrder, avm.MappingId, avm.MapType, av.sortOrder valueSortOrder, coalesce(om.MappingId, avm.MappingId) OutlineMappingId from AttributeValuesMapping avm inner join AttributeValues av on avm.AttributeValueId = av.AttributeValueId inner join AttributeNames an on an.attributeNameId = av.attributenameid join SubProductGroups spg1 on spg1.productid = 1607 and spg1.ismaingroup = 1 left join SubProductGroups spg on avm.MappingId = spg.GroupID and spg.ProductID = 1607 left join AttributeValuesMapping avm2 on avm2.linkedto = av.AttributeValueId and avm2.MapType = 1 and avm2.ProductID IN (1607) left join AttributeValuesMapping avm22 on (avm22.linkedto2 = av.AttributeValueId) and avm22.MapType = 1 and avm22.ProductID IN (1607) left join AttributeValuesMapping avm3 on (avm3.AttributeValueId = avm.linkedto and avm3.isDefault =1 and avm3.ProductID IN(1607)) left join AttributeValuesMapping avm4 on (avm4.AttributeValueId = avm.linkedto2 and avm4.isDefault =1 and avm4.ProductID IN(1607)) left join OutlineMapping om on om.outlinemapid = avm.MappingId and om.MapType = 2 and om.productID = 1607 where avm.productid In(1607) and avm.MapType = 1 /*and coalesce(isMainGroup,0) = 1*/ and (avm3.AttributeValueId is not null or isnull(avm.linkedto,0) = 0) and (avm4.AttributeValueId is not null or isnull(avm.linkedto2,0) = 0) order by an.sortOrder,an.attributeGroup,av.AttributeNameId, av.sortOrder

sqlAttributes: select distinct avm.AttributeMappingID,AttributeName,av.AttributeNameId,AttributeValue, an.type,cssProperty, isnull(avm2.linkedto, 0) isLink, sub.subproductname SubProductGroupName, 0 isMainGroup, av.AttributeValueId, attributeValueName, avm.isDefault, avm.Price, controlType, attributeGroup, Explanation, isnull(avm.linkedTo,0) linkedTo, an.sortOrder, avm.MappingId, avm.MapType, av.sortOrder valueSortOrder, coalesce(om.MappingId, avm.MappingId) OutlineMappingId from AttributeValuesMapping avm inner join AttributeValues av on avm.AttributeValueId = av.AttributeValueId inner join AttributeNames an on an.attributeNameId = av.attributenameid inner join SubProducts sub on sub.SubProductId = avm.MappingId inner join SubProductGroups spg on sub.GroupID = spg.GroupID left join AttributeValuesMapping avm2 on avm2.linkedto = av.AttributeValueId and avm2.ProductID IN (1607) left join AttributeValuesMapping avm3 on (avm3.AttributeValueId = avm.linkedto and avm3.isDefault =1 and avm3.MapType = 2 and avm3.ProductID IN (1607)) left join OutlineMapping om on om.outlinemapid = avm.MappingId and om.MapType = 2 and om.productID = 1607 where avm.productid IN (1607) and avm.MapType = 2 and sub.Required = 1 order by coalesce(om.MappingId,avm.MappingId), an.sortOrder,an.attributeGroup,av.AttributeNameId, av.sortOrder

sqlAttributes: select distinct avm.AttributeMappingID,avm.mappingid,AttributeName,av.AttributeNameId,AttributeValue, an.type,cssProperty, 0 isLink, av.AttributeValueId, attributeValueName, avm.isDefault, avm.Price, controlType, attributeGroup, Explanation, isnull(avm.linkedTo,0) linkedTo, an.sortOrder,avm.MappingId MappingId, avm.MapType, av.sortOrder, coalesce(om.MappingId, avm.MappingId) OutlineMappingId from AttributeValuesMapping avm inner join AttributeValues av on avm.AttributeValueId = av.AttributeValueId inner join AttributeNames an on an.attributeNameId = av.attributenameid inner join Paragraphs p on p.ParagraphId = avm.MappingId inner join SubProducts sp on sp.SubProductId = p.SubProductId left join OutlineMapping om on om.outlinemapid = avm.MappingId and om.MapType = 2 and om.productID = 1607 where avm.productid IN (1607) and avm.MapType = 3 and (sp.Required = 1 )order by coalesce(om.MappingId,avm.MappingId), an.sortOrder,av.AttributeNameId, av.sortOrder