Join two tables and such that only one row from 2nd table is joined

I have Two tables, MachineWiseMaterial (Fields: McMatID) and MaterialIssueRegister (Fields: McMatID, IssueDate, IssueTime, EMR). I want the last issue date, time and EMR for each McMatID.

I can find the same for any individual McMatID using SELECT TOP 1 as follows.

SELECT TOP 1 McMatID, IssueDate, IssueTime, EMR 
FROM MaterialIssueRegister 
WHERE McMatID = [some value, eg. 1] 
ORDER BY IssueDate DESC, IssueTime DESC, EMR DESC;

But when I’m trying to join both tables, I’m not getting desired results.

SELECT MachineWiseMaterial.McMatID, b.IssueDate, b.EMR 
FROM MachineWiseMaterial 
LEFT JOIN (SELECT TOP 1 McMatID, IssueDate, IssueTime, EMR 
FROM MaterialIssueRegister  
ORDER BY IssueDate DESC, IssueTime DESC, EMR DESC) AS b
ON MachineWiseMaterial.McMatID = b.McMatID;

The above query returns only those McMatID whose IssueDate are Max and not the max date for each McMatID.

Answer

You could try like this..

SELECT MachineWiseMaterial.McMatID, b.IssueDate, b.EMR 
FROM MachineWiseMaterial m1
where m1.McMatId=(SELECT TOP 1 McMatID
FROM MaterialIssueRegister  m2
where m2.McMatId=m1.McMatId
ORDER BY IssueDate DESC, IssueTime DESC, EMR DESC)