Embedding SELECT statement into WHERE clause

I'm trying to embed a select statement with a JOIN into a where statement.

Not sure what the best method is for this, open to suggestions.

Thank you.

WHERE
shitem.forderqty=(SELECT
Sum(shitem.fshipqty)
FROM sorels
INNER JOIN (shmast INNER JOIN shitem ON shmast.fshipno = shitem.fshipno) ON (sorels.fsono = shmast.fcsono) AND (sorels.fenumber = shitem.fenumber))

Something like this, or you could put the derived table into a CTE:

SELECT *
FROM ...
JOIN (
SELECT
Sum(shitem.fshipqty) AS fshipqty
FROM sorels
INNER JOIN shmast ON sorels.fsono = shmast.fcsono
INNER JOIN shitem ON shmast.fshipno = shitem.fshipno AND sorels.fenumber = shitem.fenumber
) t ON shitem.forderqty=t.fshipqty

ugh, when i add

JOIN (
SELECT
Sum(shitem.fshipqty) AS fshipqty
FROM sorels
INNER JOIN shmast ON sorels.fsono = shmast.fcsono
INNER JOIN shitem ON shmast.fshipno = shitem.fshipno AND sorels.fenumber = shitem.fenumber
) t ON shitem.forderqty=t.fshipqty

as a new join i get no returns, what am I missing?

You'll have to post sample data and expected result for us to help. Plus show us the rest of the query.

Thanks for your help Tara!

Not sure how to provide sample data but here's the whole query:

select
soitem.fac as [Fac],
somast.fcompany as [Customer],
shmast.fccompany as [End Customer],
soitem.fsono as [SO No],
soitem.fenumber as [Line Item],
soitem.fquantity as [SO Qty],
shitem.fshipqty as [Ship Qty],
sorels.fnetprice as [Sales Price],
soitem.fpartno as [Part No],
soitem.fpartrev as [Rev],
soitem.fprodcl as [Prod Cls],
soitem.fdesc as [Desc],
shmast.fshipdate as [Ship Dt],
SHITEM.FITEMNO AS [Ship Ln Item],
soitem_ext.field3 as [Prom Dt],
soitem_ext.field9 as [Reason],

CASE

	WHEN shmast.fshipdate >= '20150731'
		AND shmast.fshipdate < '20150829' THEN '1'
		
	WHEN shmast.fshipdate >= '20150830'
		AND shmast.fshipdate < '20150927' THEN '2'
		
	WHEN shmast.fshipdate >= '20150927'
		AND shmast.fshipdate < '20151031' THEN '3'
		
	WHEN shmast.fshipdate >= '20151101'
		AND shmast.fshipdate < '20151128' THEN '4'
		
		WHEN shmast.fshipdate >= '20151129'
		AND shmast.fshipdate < '20151226' THEN '5'
		
		WHEN shmast.fshipdate >= '20151227'
		AND shmast.fshipdate < '20160130' THEN '6'
		
		WHEN shmast.fshipdate >= '20160201'
		AND shmast.fshipdate < '20160227' THEN '7'
		
		WHEN shmast.fshipdate >= '20160228'
		AND shmast.fshipdate < '20160326' THEN '8'
		
		WHEN shmast.fshipdate >= '20160327'
		AND shmast.fshipdate < '20160430' THEN '9'
		
		WHEN shmast.fshipdate >= '20160501'
		AND shmast.fshipdate < '20160528' THEN '10'
		
		WHEN shmast.fshipdate >= '20160529'
		AND shmast.fshipdate < '20160625' THEN '11'
		
		WHEN shmast.fshipdate >= '20160626'
		AND shmast.fshipdate < '20160801' THEN '12'
	ELSE 'X'
END AS FM,
CASE 
	WHEN shmast.fshipdate >= '20140801'
		AND shmast.fshipdate < '20150801' THEN '2015'
	WHEN shmast.fshipdate >= '20150801'
		AND shmast.fshipdate < '20160731' THEN '2016'
	ELSE 'X'
END AS FY,

DATEDIFF(DD, shmast.fshipdate,SOITEM_EXT.FIELD3) AS [Days Late],
/CHANGES*****************/

CASE
when shmast.fshipdate > SOITEM_EXT.FIELD3 then '1'
else '0'
end as [Days_Early_Count]*/

/END CHANGES********/
from soitem

JOIN soitem_ext--l
on soitem_ext.fkey_id=soitem.identity_column
--and soitem_ext.field3<>cast('1900-01-01 00:00:00' as datetime)

LEFT JOIN somast--l
on somast.fsono=soitem.fsono

JOIN shmast
on shmast.fcsono=somast.fsono
and shmast.fcsorev=somast.fsorev
--and shmast.fshipdate>soitem_ext.field3

LEFT JOIN sorels--l
on sorels.fsono=soitem.fsono
and sorels.fpartno=soitem.fpartno
and sorels.finumber=soitem.finumber

JOIN shitem ON
shmast.fshipno = shitem.fshipno
and
sorels.fsono = shmast.fcsono
and
sorels.fenumber = shitem.fenumber
and
soitem.fenumber = shitem.fenumber

JOIN (
SELECT
Sum(shitem.fshipqty) AS fshipqty
FROM sorels
INNER JOIN shmast ON sorels.fsono = shmast.fcsono
INNER JOIN shitem ON shmast.fshipno = shitem.fshipno AND sorels.fenumber = shitem.fenumber
) t ON shitem.forderqty=t.fshipqty

where
soitem.fprodcl not in ('54','55','70','71','72','73','76','77','78','79','7A','FR','SH')
and
shmast.fshipdate BETWEEN '4/16/2016' AND '4/24/2016'

order by soitem.fsono ASC