Can this be done (Left Join on a sub select query with a where? The Bold is stating that Multi-part identifier cannot be bound..Not sure how to re-write this to get the same results, other than below in my "Non-efficient way"
select R.URICode, briefdescription,
BillCode, model, ManName, SERIALNUMBER,HOMELOCATION, G1.Scan_Date,G1.Current_Location, G1.JobNumber
from NewInventory R
left outer join
(Select top 1 Scan_Date, Current_Location, Filepath1 as JobNumber, UPCCode from Scans e where e.upccode = R.URICode order by Scan_Date desc ) G1 on R.URICODE = G1.UPCCode
where R.URICode <='30126'
order by R.URICode asc
Non-Efficient Way:
select b.URICode, briefdescription, BillCode, model, ManName, SERIALNUMBER,HOMELOCATION,
(select top 1 Scan_Date from Scans e where upccode = b.uricode order by Scan_Date desc)as Last_Scan_Date,
(select top 1 Current_Location from Scans e where upccode = b.uricode order by Scan_Date desc)as Current_Location,
(select top 1 FilePath1 as JobNumber from Scans e where upccode = b.uricode order by Scan_Date desc)as JobNumber
from NewInventory b
left join urijobs.dbo.jobs on (select top 1 FilePath1 as JobNumber from Scans e where upccode = b.uricode order by Scan_Date desc) = urijobs.dbo.jobs.Job_Number
left join urijobs.dbo.Job_Status on urijobs.dbo.Job_Status.Status_ID = urijobs.dbo.jobs.[status]
where b.URICode <='30126'
order by b.URICode asc
But there's also no need for the subquery:
Open in new window