Need help with MAX in a join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help with MAX in a join

Author

Topic

jedinerd
Starting Member

4 Posts

Posted - 2009-06-29 : 12:22:36

Well, the MAx command isn't behaving as i would expect it to. In the query below i would only expect one row returned for each value of Alert due to the MAX group by ALert , but that is not the case. Can anyone point me in the direction of the light?

select dbo.Device.Name as DeviceName, dbo.DeviceGroup.Name as GroupName, dbo.Employee.Name as Employee, newhist.Reason as Reason, Alerting.Type.Name as Type
from Alerting.HeldDetail LEFT JOIN ( select h.StatusTime, h.StatusUser, h.Reason , h1.Alert from Alerting.History AS h RIGHT JOIN ( select h1.Alert, MAX(h1.StatusTime) as statustime from Alerting.history as h1 group by h1.Alert ) As h1 ON h.Alert=h1.Alert)
as newhist ON Alerting.HeldDetail.Alert=newhist.Alert 
LEFT JOIN Alerting.Message on Alerting.HeldDetail.Alert=Alerting.Message.Alert 
LEFT JOIN dbo.Device on Alerting.Message.Device=dbo.Device.ID
LEFT JOIN dbo.DeviceGroup on dbo.Device.DeviceGroup=dbo.DeviceGroup.id
LEFT JOIN dbo.Employee on newhist.StatusUser=dbo.Employee.id
LEFT JOIN Alerting.Alert on Alerting.HeldDetail.Alert=Alerting.Alert.ID
LEFT JOIN Alerting.Type on Alerting.Alert.Type=Alerting.Type.ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-29 : 12:28:28

you're still joining on repeated field alone. i think you need this modification


select dbo.Device.Name as DeviceName, 
dbo.DeviceGroup.Name as GroupName, 
dbo.Employee.Name as Employee, 
newhist.Reason as Reason, 
Alerting.Type.Name as Type
from Alerting.HeldDetail 
LEFT JOIN ( 
select h.StatusTime, h.StatusUser, h.Reason , h1.Alert 
from Alerting.History AS h 
RIGHT JOIN ( select Alert, MAX(StatusTime) as statustime 
from Alerting.history 
group by h1.Alert ) As h1 
ON h.Alert=h1.Alert
AND h.StatusTime=h1.statustime)as newhist 
ON Alerting.HeldDetail.Alert=newhist.Alert 
LEFT JOIN Alerting.Message on Alerting.HeldDetail.Alert=Alerting.Message.Alert 
LEFT JOIN dbo.Device on Alerting.Message.Device=dbo.Device.ID
LEFT JOIN dbo.DeviceGroup on dbo.Device.DeviceGroup=dbo.DeviceGroup.id
LEFT JOIN dbo.Employee on newhist.StatusUser=dbo.Employee.id
LEFT JOIN Alerting.Alert on Alerting.HeldDetail.Alert=Alerting.Alert.ID
LEFT JOIN Alerting.Type on Alerting.Alert.Type=Alerting.Type.ID

jedinerd
Starting Member

4 Posts

Posted - 2009-06-29 : 12:34:54

That fixed it right up! Thank you so much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-29 : 12:36:44

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources