I have a table that has the following fields:
CaseHistoryID
TicketID
DateTime
UserID
TicketTime
OldCaseStatusID
NewCaseStatusID
OldUserID
NewUserID
I am trying to run the following query:
SELECT CaseHistory.DateTime, CaseStatus.CaseStatusName AS
OLdCaseStatusName, CaseStatus.CaseStatusName AS NewStatus,
Users.Username AS OldUserName, Users.Username AS NewUserName
FROM CaseHistory LEFT OUTER JOIN
Users ON CaseHistory.OldUserID = Users.UserID AND
CaseHistory.NewUserID = Users.UserID LEFT OUTER JOIN
CaseStatus ON CaseHistory.OldCaseStatusID =
CaseStatus.CaseStatusID AND
CaseHistory.NewCaseStatusID = CaseStatus.CaseStatusID
WHERE CaseHistory.TicketID = 7
The result that I want to see is:
DateTime OldCaseStatus NewCaseStatus
OldUserName NewUserName
5/22/2008 Opened Reassigned
johndoe janedoe
All but the datetime field are showing NULL although I have values in
the casestatus and userID fields. Can a SQL guru point out what I am
doing wrong? I tried right, left and full joins to no avail.
Thanks,
Bruce
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:3077
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
