SELECT
m.musym AS Map_unit_symbol, m.muname AS Map_unit_name,
CONVERT(VARCHAR(3), slope_l) + ' to ' + CONVERT(VARCHAR(3),slope_h) AS Slope,
(c.nirrcapcl + c.nirrcapscl) AS LCC,
(SELECT TOP 1 cy.nonirryield_r
FROM cocropyld AS cy
WHERE c.cokey=cy.cokey AND cropname LIKE 'Corn') AS Corn_YLD,
(SELECT TOP 1 cy2.cropprodindex
FROM cocropyld AS cy2
WHERE c.cokey=cy2.cokey AND cropname LIKE 'corn') AS Corn_Prod_Index,
farmlndcl AS Farm_land_class, m.muacres AS Map_unit_acres,
ROUND((CAST(muacres*100 AS FLOAT)/areaacres),2) AS Map_unit_percent,
compname AS Component_name,
(SELECT top 1 texture
FROM chorizon AS ch
LEFT OUTER JOIN chtexturegrp AS cht ON ch.chkey = cht.chkey
WHERE ch.cokey = c.cokey ORDER BY rvindicator DESC) AS Texture,
LEFT(l.areASymbol, 2) AS 'State',
RIGHT(l.areASymbol, 3) AS 'County', l.areaname AS Area_name, l.areaacres AS Area_acres
FROM legend AS l
INNER JOIN mapunit AS m ON l.lkey = m.lkey
INNER JOIN component AS c ON m.mukey = c.mukey
WHERE l.areASymbol=
'IN011'
AND c.majcompflag='Yes' AND
cokey = (SELECT top 1 cokey FROM component AS c3 WHERE c3.mukey=m.mukey ORDER BY comppct_r DESC, cokey)
ORDER BY l.areASymbol, m.muname, c.comppct_r DESC