sql - Subquery not showing all Results -

sql - Subquery not showing all Results -

i have subquery takes in item number 1 table , uses in clause result main table. however, subquery not homecoming right results. here's query , example:

,( select top 1[unitprce]from [nimet].[dbo].[sop10200] [nimet].[dbo].[sop10200].itemnmbr=tbljobhold.itemnmbr order [nimet].[dbo].[sop10200].reqshipdate desc ) itemnmbr cost 07-0008 0.48000 20232001 0.00000 22241457 0.00000 67181001 0.00000 lr56230-095 2.36000 s-af509 0.59000 s24a7869a1 0.00000

this returns results, however, when take 1 of results shows 0 , run against table, result.

select top 1 [unitprce]from [nimet].[dbo].[sop10200] itemnmbr='20232001' order reqshipdate desc unitprce 2.10000

why result not beingness returned subquery?

here's whole query. pretty short.

declare @start datetime, @end datetime set @start =convert(datetime,'10/13/2014') set @end =convert(datetime,'10/14/2014') select tbljobhold.[manufactureorder_i] ,[holdqty] ,tbljobhold.[itemnmbr] ,[itemdesc] ,[onholdbyname] ,[onholddate] ,[problem] ,[cause] ,[resolution] ,[notes] ,[releasedbyname] ,[releaseddate] ,(select top 1 [unitprce]from [nimet].[dbo].[sop10200] itemnmbr=tbljobhold.itemnmbr order reqshipdate desc) [nimetdata].[dbo].[tbljobhold] [releaseddate] >= @start , [releaseddate] < @end , resolution in ('not found', 'scrap-nimet','scrap-pioneeer','scrap-pioneer','scrap-customer','rework')

sop10200 hold prices each of items based on lastly shipped date. trying corresponding cost latest date using part number tbljobhold table. both have matching part numbers.

i added sec subquery table , returns values on each instance. seems ust cost 1 broke

,(select top 1 [shiptoname] [nimet].[dbo].[sop10200] itemnmbr=tbljobhold.itemnmbr order reqshipdate desc) 'ship to'

sql sql-server sql-server-2005