makelei makelei - 22 days ago 17
SQL Question

Join 2 tables with where and MAX

I have 2 tables;

TableSN

snID SerialNR Unit Version
1 123 A A1
2 456 A A2


TableDelivery

dID SerialNR Team
1 456 US
2 456 GE
3 456 FI


Result

snID SerialNR Unit Version Team
1 123 A A1
2 456 A A2 FI


I need to get table based on TableSN and SerialNR, listing where Version is given. In the same table I need to show latest entered data (= MAX(dID)) for Team-column. I am able to get table to show correctly when there is Team-data available.

My query is like this



SELECT TableSN.snID, TableSN.SerialNR, TableSN.Unit, TableSN.Version, TableDelivery.Team
FROM TableSN LEFT OUTER JOIN TableDelivery ON TableDelivery. SerialNR = TableSN. SerialNR
LEFT OUTER JOIN (SELECT SerialNR, MAX(dID) AS snRank
FROM TableDelivery AS TableDelivery _1
GROUP BY SerialNR) AS NewTeam ON TableDelivery. SerialNR = NewTeam. SerialNR AND TableDelivery.dID = NewTeam.snRank
WHERE (TableSN.Version = @Version)


How do I get SerialNR visible where there are no Team available?
Thank you for guidance and support.

This is what is needed:

[enter image description here]

Answer

Try this :

SELECT TableSN.snID, TableSN.SerialNR, TableSN.Unit, TableSN.Version, TableDelivery.Team
FROM TableSN 
LEFT JOIN TableDelivery ON TableDelivery.SerialNR = TableSN. SerialNR AND 
                           TableDelivery.dID = (SELECT MAX(dID) 
                                                FROM TableDelivery AS t         
                                                WHERE t.SerialNR = TableSN. SerialNR)
WHERE (TableSN.Version = @Version)
Comments