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

Question

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.

I dont know the syntax of SQL in access, but im pretty your probleme is that your are not using `GROUP BY McMatId` to group all the id and then take the Max date of each. Actually `(SELECT TOP 1 McMatID, IssueDate, IssueTime, EMR FROM MaterialIssueRegister ORDER BY IssueDate DESC, IssueTime DESC, EMR DESC) AS b` will always contain only 1 id because you only select the Top 1, no matters how much different id there are. — Matriac, Jun 07 '16 at 02:34
Possible duplicate of [SQL Server: How to Join to first row](http://stackoverflow.com/questions/2043259/sql-server-how-to-join-to-first-row) — Marc L., Jun 07 '16 at 02:49
@Matriac Thanks for the answer. I realised my mistake just after posting and forgot about this post for ever. I never used this account there after. Really Sorry for that. — Saumya Kanta Swain, Jan 08 '20 at 09:34

score 0 · Accepted Answer · answered Jun 07 '16 at 02:57

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)

score 0 · Answer 2 · answered Jun 07 '16 at 06:10

0

what about something like this:

select MachineWiseMaterial.McMatID, b.EMR , max(b.IssueDate) FROM MachineWiseMaterial LEFT JOIN MaterialIssueRegister b ON MachineWiseMaterial.McMatID = b.McMatID group by MachineWiseMaterial.McMatID, b.EMR

answered Jun 07 '16 at 06:10

JScut

1
2

Thanks for the answer. I realised my mistake just after posting and forgot about this post for ever. I never used this account there after. Really Sorry for that. – Saumya Kanta Swain Jan 08 '20 at 09:34

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

2 Answers2