You're joining to the same thing for old/new for both Users and
CaseStatus, which wont work unless they're the same (and, by
definition, they're rather unlikely to be).
Try this:
SELECT
ch.DateTime,
old_cs.CaseStatusName AS OldCaseStatusName,
new_cs.CaseStatusName AS NewStatus,
old_u.Username AS OldUserName,
new_u.Username AS NewUserName
FROM CaseHistory ch
LEFT OUTER JOIN Users old_u
ON ch.OldUserId = old_u.UserID
LEFT OUTER JOIN Users new_u
ON ch.NewUserId = new_u.UserID
LEFT OUTER JOIN CaseStatus old_cs
ON ch.OldCaseStatusID = old_cs.CaseStatusID
LEFT OUTER JOIN CaseStatus new_cs
ON ch.NewCaseStatusID = new_cs.CaseStatusID
WHERE ch.TicketID = 7
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k
Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:3078
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
