Joining a subquery with a join statement

Question

Attempting to apply a subquery to table for the purpose of being able to group it.

Main Query

 SELECT soitem.fsono, fpartno, soitem.fcfromno, finumber
 FROM soitem 
 INNER JOIN somast on soitem.fsono = somast.fsono  
 WHERE somast.fsono='034313'  
 ORDER by soitem.fsono, soitem.finumber

Subquery

SELECT min(sq.finumber), sq.fcfromno 
FROM soitem sq 
WHERE sq.fsono='034313' 
GROUP BY sq.fcfromno   

I want to use the min_finumber to further group my data set. This should appear as three rows. I used my original question as way to incorporate the subquery into my overall code. My main goal is being able to successfully group the min_number

SELECT somast.fsono, 
somast.fcustno, 
somast.fcontact, 
somast.fcustpono, 
somast.fshipvia, 
somast.forderdate, 
somast.fduedate,
 soship.fccompany, 
 soship.fcphone, 
  REPLACE(REPLACE(CONVERT(VARCHAR(MAX), soship.fmstreet), CHAR(13), '|'), CHAR(10), ' ') AS Street,
soship.fccity,
 soship.fcstate, 
 soship.fczip,
 CAST(somast.fackmemo as CHAR(35)) as ShipCode, 
 somast.fordername,
  somast.fcusrchr2,
somast.fcusrchr3,
somast.fcusrchr1, 
somast.festimator, 
soitem.fcfromno, 
 soitem.fcfromtype,
  CASE WHEN soitem.fcfromtype <> 'IFPKIT' THEN soitem.fpartno ELSE soitem.fcfromno END AS Item, 
 CASE WHEN soitem.fcfromtype <> 'IFPKIT' THEN soitem.fquantity ELSE soitem.fquantity / soitem.fquoteqty END AS Qty, 
CASE WHEN soitem.fcfromtype <> 'IFPKIT' 
THEN CAST(soitem.fdesc as CHAR(35))
ELSE 'Store Set (Phantom)' END as Descr

FROM soitem INNER JOIN
 somast ON soitem.fsono = somast.fsono LEFT OUTER JOIN
 soship ON somast.fsono = soship.fcsono AND soship.fcenumber = ''

WHERE   
(somast.fstatus <> 'Cancelled') AND (somast.fsocoord = 'IFP' OR
 somast.fsocoord = '711')
 Group By REPLACE(REPLACE(CONVERT(VARCHAR(MAX), soship.fmstreet), CHAR(13), '|'), CHAR(10), ' '),
 CAST(somast.fackmemo as CHAR(35)),
 somast.fsono,
somast.fcustno,
fcontact,
fcustpono,
fshipvia,
forderdate,
somast.fduedate,

CASE WHEN soitem.fcfromtype<>'IFPKIT' 
THEN soitem.fpartno 
ELSE soitem.fcfromno END,

CASE WHEN soitem.fcfromtype<>'IFPKIT' 
THEN soitem.fquantity 
ELSE soitem.fquantity/soitem.fquoteqty END,

CASE WHEN soitem.fcfromtype <> 'IFPKIT' 
THEN CAST(soitem.fdesc as CHAR(35))
ELSE 'Store Set (Phantom)' END,

soship.fccity, 
soship.fcstate, 
soship.fczip,
fordername, 
fcusrchr2, 
fcusrchr3,
 fcusrchr1, 
festimator,
soitem.fcfromno, 
soitem.fcfromtype,
 soship.fccompany,
  soship.fcphone

ORDER BY somast.forderdate, somast.fsono, soitem.fcfromno

Answer 1

I ended up not using a join or subquery. I was able to get the results I needed by adding min(finumber) as min_fin to the SELECTand ORDER BY

select soitem.fsono, 
somast.fcustno, 
somast.fcontact, 
somast.fcustpono, 
somast.fshipvia, 
somast.forderdate, 
somast.fduedate,
 soship.fccompany, 
 soship.fcphone, 
REPLACE(REPLACE(CONVERT(VARCHAR(MAX), soship.fmstreet), CHAR(13), '|'), CHAR(10), ' ') AS Street,
soship.fccity,
 soship.fcstate, 
 soship.fczip,
CAST(somast.fackmemo as CHAR(35)) as ShipCode,
CASE WHEN soitem.fcfromtype <> 'IFPKIT' THEN soitem.fpartno ELSE soitem.fcfromno END AS Item, 
CASE WHEN soitem.fcfromtype <> 'IFPKIT' THEN soitem.fquantity ELSE soitem.fquantity / soitem.fquoteqty END AS Qty, 
CASE WHEN soitem.fcfromtype <> 'IFPKIT' 
THEN CAST(soitem.fdesc as CHAR(35))
ELSE 'Store Set (Phantom)' END as Descr,
somast.fordername,
  somast.fcusrchr2,
somast.fcusrchr3,
somast.fcusrchr1, 
somast.festimator, 
soitem.fcfromno, 
soitem.fcfromtype, 

**min(finumber)as min_fin** 

from soitem INNER JOIN
 somast ON soitem.fsono = somast.fsono
 LEFT OUTER JOIN
 soship ON somast.fsono = soship.fcsono AND soship.fcenumber = ''

  where soitem.fsono='034313' 

group by soitem.fsono, CASE WHEN soitem.fcfromtype <> 'IFPKIT' THEN soitem.fpartno ELSE soitem.fcfromno END, 
soitem.fcfromno, soitem.fcfromtype,
somast.fcustno, 
somast.fcontact, 
somast.fcustpono, 
somast.fshipvia, 
somast.forderdate, 
somast.fduedate,
 soship.fccompany, 
 soship.fcphone,
 REPLACE(REPLACE(CONVERT(VARCHAR(MAX), soship.fmstreet), CHAR(13), '|'), CHAR(10), ' '),
soship.fccity,
 soship.fcstate, 
 soship.fczip,
 CAST(somast.fackmemo as CHAR(35)),
 CASE WHEN soitem.fcfromtype <> 'IFPKIT' THEN soitem.fquantity ELSE soitem.fquantity / soitem.fquoteqty END , 
CASE WHEN soitem.fcfromtype <> 'IFPKIT' 
THEN CAST(soitem.fdesc as CHAR(35))
ELSE 'Store Set (Phantom)' END, 
 somast.fordername,
  somast.fcusrchr2,
somast.fcusrchr3,
somast.fcusrchr1, 
somast.festimator

order by soitem.fsono, **min_fin**;