SQLTeam.com | Weblogs | Forums

How to combine a filed from two table without repeating other information

Here is my Query:

/*

select
a.siteid,
ast.vendor,
d.name as [Vendor-Name],
ast.modelnum,
ast.description,
a.wonum,
Cast(a.reportdate as Date) as [Report-Date],
a.description AS request,
a.assetnum,
a.maintby,
a.worktype,
a.totaldwntime AS downtime,
a.status,
c.ldtext AS worklog,
Cast(b.createdate as date) as [Log-Entry-Date],
p1.displayname AS Who
--b.worklogid
FROM dbo.workorder AS a
LEFT OUTER JOIN dbo.worklog AS b ON a.wonum = b.recordkey --OR (b.recordkey is null)
LEFT OUTER JOIN dbo.longdescription AS c ON b.worklogid = c.ldkey
LEFT OUTER JOIN dbo.person AS p1 ON b.createby = p1.personid
LEFT OUTER JOIN asset as ast ON a.assetnum = ast.assetnum
LEFT OUTER JOIN dbo.companies as d ON ast.vendor = d.company
WHERE (c.ldownertable ='worklog') AND (a.wonum in (select wonum from dbo.workorder as d group by wonum having (a.worktype not in ('ppm','mpm','ds','tspm')) OR (b.recordkey is null)
AND (a.reportdate > DATEADD(YEAR,-2,GETDATE())) AND (a.istask =0) )) AND (a.siteid in ('p202','p203'))
AND (a.maintby not in ('ms'))
ORDER BY a.reportdate desc, a.siteid asc, ast.modelnum, a.assetnum

*/

/* Result*/

siteid vendor Vendor-Name modelnum description wonum Report-Date request assetnum maintby worktype downtime status worklog Log-Entry-Date Who
P202 202963 Conveyor Technologies Inc. DS-.8M-RL-2232 CTI DOUBLE DESTACKER 6215810 2020-12-03 Machine not dropping boards 561/13/005 SA TSCM NULL TS_RVW Made adjustment to gap where boards would drop. Started back production. 2020-12-03 Michael Bailey
P202 202963 Conveyor Technologies Inc. DS-.8M-RL-2232 CTI DOUBLE DESTACKER 6215810 2020-12-03 Machine not dropping boards 561/13/005 SA TSCM NULL TS_RVW Called back an hour later and found gap needed to be adjusted again. Found the gears were sticking while adjusting. Called Maintenance to check out gears. 2020-12-03 Michael Bailey

It duplicates the record due to the left join and all I want to id capture the worklog and show in one record.

Any help or guidance is appreciated.

Thanks
Landon H1

with knowing anything about your data model, you could try distinct or add row numbers and then take the first rownum of each group

Welcome to forum. help us help you by providing us real or near to real sample data

so there are two work logs entries for MBailey. Which one do you want and why?