SQL Query - having trouble with this one

Hi
I'm trying to run a query out of our ERP system for workorders, and then a few summary columns of what type of work the workorders contain.

The problem I'm having (see image), is for each workorder (from the COSS_Workorder table), I'm getting duplicate rows.  If you look at the picture, there's one row where all the right columns are "NULL".  Then there's a row where "Heat Treat" column has an "X" in it, and the other are null, and so forth.  For some workorders, like W0005287-003, there are four rows, one for each.

A breakdown of the database - table 'b' has multiple items (line items) for each item in table 'a' (each workorder).   Then the link between b and c is a one to one, and c to d is a one to one.  Table e is just linked to pull the part type for each workorder.

WHAT I'M TRYING TO GET TO:  Is a summary of each workorder.  So one line per workorder, with an 'X' in whichever column it needs - so if it needs manufacturing, there'd be an 'X'.  If it needs machining, there'd be an 'X'.  What exactly it needs is defined in the CASE statements, based on the operation (table 'd') name (or 'd.UsrDfnId')

Code is below:

select DISTINCT
a.UsrDfnId as 'Workorder',
a.CustomerName as 'Customer',
a.DrawName as 'Drawing',
e.Description as 'Part Type',
a.createtime as 'Created',
CASE 
	when d.UsrDfnId = 'Manuf%' then 'X'
	end as 'Contains Manufacturing',
CASE 
	when d.UsrDfnId like 'Mach%' then 'X'
	end as 'Contains Machining',
CASE 
	when d.UsrDfnId like 'TEST%' then 'X'
	end as 'Contains Testing',
CASE
	when d.UsrDfnId like 'HT%' then 'X'
	end as 'Contains Heat Treat',	
CASE
	when d.UsrDfnId = 'QTC-TB' then 'X'
	end as 'Requires Test Bar',
CASE
	when d.UsrDfnId like 'TREPAN%' OR d.UsrDfnId = 'QTC-TREPAN' then 'X'
	end as 'Requires Trepan',
CASE
	when d.UsrDfnId like 'OSP-%' OR d.Description like 'External %' then 'X'
	end as 'Requires Outside Processing'
from COSS_WorkOrder a
join COSS_WorkOrderLineItem b
on a.ident = b.WoRefIdent
join COSS_WorkOrderOprtSvcDetail c
on b.detailinfoident = c.ident
join coss_operation d
on c.OprtIdent = d.ident
join COSS_PartType e
on a.PartTypeIdent = e.ident
where a.createtime > '2015-06-01'
order by a.createtime, a.UsrDfnId

Select allOpen in new window

issue.png