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.
| Author |
Topic |
|
darren.stephens
Starting Member
4 Posts |
 Posted - 2009-09-17 : 00:37:43
|
| Hi have sql statement below which is returning multiple lines due to notes table having multiple entries to one workorder id table format egwoworkoderidnotesworkorderidnotedatenotejoins with workorder id but have multiplewould like to join and report one entry would be nice to pick up last note entry for workorderidSELECT ti.FIRST_NAME AS Technician, wo.WORKORDERID AS [Request ID], aau.FIRST_NAME AS Requester, dpt.DEPTNAME AS Department, cd.CATEGORYNAME AS Category, scd.NAME AS [Sub Category], icd.NAME AS Item, wo.TITLE AS Subject, urgdef.NAME AS Urgency, impdef.NAME AS Impact, rtdef.NAME AS [Request Type], ti.FIRST_NAME, wotodesc.FULLDESCRIPTION AS Description, pd.PRIORITYNAME AS Priority, std.STATUSNAME AS [Request Status], DATEADD(SECOND, wo.CREATEDTIME / 1000, '19700101 10:00') AS [Created Time], DATEADD(SECOND, wo.RESPONDEDTIME / 1000, '19700101 10:00') AS [Responded Date], wo.TIMESPENTONREQ AS [Time Spent], wos.ISOVERDUE AS [Overdue Status], dbo.Notes.NOTESTEXT AS [note text], DATEADD(SECOND, dbo.Notes.NOTESDATE / 1000, '19700101 10:00') AS [note Time]FROM dbo.CategoryDefinition AS cd RIGHT OUTER JOIN dbo.WorkOrderStates AS wos ON cd.CATEGORYID = wos.CATEGORYID LEFT OUTER JOIN dbo.SubCategoryDefinition AS scd ON wos.SUBCATEGORYID = scd.SUBCATEGORYID LEFT OUTER JOIN dbo.ItemDefinition AS icd ON wos.ITEMID = icd.ITEMID LEFT OUTER JOIN dbo.UrgencyDefinition AS urgdef ON wos.URGENCYID = urgdef.URGENCYID LEFT OUTER JOIN dbo.ImpactDefinition AS impdef ON wos.IMPACTID = impdef.IMPACTID LEFT OUTER JOIN dbo.RequestTypeDefinition AS rtdef ON wos.REQUESTTYPEID = rtdef.REQUESTTYPEID LEFT OUTER JOIN dbo.PriorityDefinition AS pd ON wos.PRIORITYID = pd.PRIORITYID LEFT OUTER JOIN dbo.StatusDefinition AS std ON wos.STATUSID = std.STATUSID FULL OUTER JOIN dbo.WorkOrder_Threaded AS wot INNER JOIN dbo.WorkOrder AS wo ON wot.WORKORDERID = wo.WORKORDERID INNER JOIN dbo.Notes ON wo.WORKORDERID = dbo.Notes.WORKORDERID INNER JOIN dbo.SDUser AS sdu ON wo.REQUESTERID = sdu.USERID LEFT OUTER JOIN dbo.AaaUser AS aau ON sdu.USERID = aau.USER_ID LEFT OUTER JOIN dbo.DepartmentDefinition AS dpt ON wo.DEPTID = dpt.DEPTID LEFT OUTER JOIN dbo.WorkOrderToDescription AS wotodesc ON wo.WORKORDERID = wotodesc.WORKORDERID ON wos.WORKORDERID = wo.WORKORDERID FULL OUTER JOIN dbo.AaaUser AS ti RIGHT OUTER JOIN dbo.SDUser AS td ON ti.USER_ID = td.USERID ON wos.OWNERID = td.USERID |
|
|
|
|
|
|
|
joining tables with one to many relationship