Select Top 1 in Left Join

tim_cs

That is what I thought but I receive all NULLS for the LEFT JOIN results.  

Is this more what you are looking for?  If not could you provide an example of the results you want?

SELECT 
	A.*, 
	(SELECT TOP(1)  B.Field_You_Want
		FROM dbo.tspvInventoryReceipts B 
	WHERE (PO_Line_Status='released' OR PO_Line_Status='change order') AND A.Item_Number = B.Item_Number
	ORDER BY date_received DESC) 
FROM 
   dbo.tspvPurchaseLineItem a
WHERE a.Item_Number NOT LIKE '*%' 

Select allOpen in new window

however, it would only show you the max date. if there is an id field for the row then what you could do is find the top date id's and pull back those records for your subquery:

so
select data you want
from table_1
left join (
 select data you want
  from table_2
  where tableid in ( select tableid from table2 a join (select itemnumber, max(datereceived) date from table2 group by itemnumber) b on b.itemnumber = a.itemnumber and b.date = a.date )
)

something like that

Ok so basically what I have is 2 tables:

1. tspvPurchaseLineItem - line items from purchase orders with item costs
2. tspvIventoryReceipts - what we paid for those items in the past

I am looking to write a query that will so me every line item in the tspvPurchaseLineItem table and then the last cost we paid for it by joining the tspvInventoryReciepts table on the item number.  

If I have received the item 10 times, however, then I will get 10 rows for each line item in the tspvPurchaseLineItem table when I join the tspvInventoryReceipts table (because I have received that item 10 times in the past).  I just want the query to show the PO line item and then last inventoryreceipt cost.

so it sounds like you should work first by getting the last receipt for all product and the amount paid at that time (like in the attached) so you have a unique list of products and the last price you paid

and then use that to join to your purchaselineitem by the itemnumber

select i.itemnumber, r.price
from tspvinventoryreceipts r
join (
select itemnumber, max(datepurchased) datepurchased
from tspvinventoryReceipts
group by itemnumber
) i on i.itemnumber = r.itemnumber and i.datepurchased = r.datepurchased

Select allOpen in new window

you can find the general approach in my article:
https://www.experts-exchange.com/articles/Database/Miscellaneous/DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html

This didn't actually work, but the idea behind it did and I figured out the query.  Thanks guys.