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

Open in new window

issue.png
Mystical_IceAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RayData AnalystCommented:
Sorry, I don't have more time to answer today but . . .

I expect your issue lies in the joins.  My guess is that there is more than one row per workorder in the tables your are joining.  You're gonna have to workout how to get your joins to only return one row, if that is possible.  Your table structure may not work out like that.  

If this has comments and is still open tomorrow, I will look at it further.  :-)  
chaauCommented:
You can achieve it in two ways. The first one is to use your query with an addition of the GROUP BY clause and aggregate function:
select 
a.UsrDfnId as 'Workorder',
a.CustomerName as 'Customer',
a.DrawName as 'Drawing',
e.Description as 'Part Type',
a.createtime as 'Created',
MAX(CASE 
	when d.UsrDfnId = 'Manuf%' then 'X'
	end) as 'Contains Manufacturing',
MAX(CASE 
	when d.UsrDfnId like 'Mach%' then 'X'
	end) as 'Contains Machining',
MAX(CASE 
	when d.UsrDfnId like 'TEST%' then 'X'
	end) as 'Contains Testing',
MAX(CASE
	when d.UsrDfnId like 'HT%' then 'X'
	end) as 'Contains Heat Treat',	
MAX(CASE
	when d.UsrDfnId = 'QTC-TB' then 'X'
	end) as 'Requires Test Bar',
MAX(CASE
	when d.UsrDfnId like 'TREPAN%' OR d.UsrDfnId = 'QTC-TREPAN' then 'X'
	end) as 'Requires Trepan',
MAX(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'
GROUP BY 
a.UsrDfnId,
a.CustomerName,
a.DrawName,
e.Description,
a.createtime
order by a.createtime, a.UsrDfnId

Open in new window

The second way of doing this is more preferable in my opinion. It is by joining the same coss_operation table multiple times filtering it by type, like this:
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 d1.ident IS NOT NULL then 'X'
	end as 'Contains Manufacturing',
CASE 
	when d2.ident IS NOT NULL  then 'X'
	end as 'Contains Machining',
CASE 
	when d3.ident IS NOT NULL then 'X'
	end as 'Contains Testing',
CASE
	when d4.ident IS NOT NULL then 'X'
	end as 'Contains Heat Treat',	
CASE
	when d5.ident IS NOT NULL then 'X'
	end as 'Requires Test Bar',
CASE
	when d6.ident IS NOT NULL then 'X'
	end as 'Requires Trepan',
CASE
	when d7.ident IS NOT NULL 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_PartType e
on a.PartTypeIdent = e.ident
left join coss_operation d1
on c.OprtIdent = d1.ident AND d1.UsrDfnId LIKE 'Manuf%'
left join coss_operation d2
on c.OprtIdent = d2.ident AND d2.UsrDfnId like 'Mach%' 
left join coss_operation d3
on c.OprtIdent = d3.ident AND d3.UsrDfnId like 'TEST%' 
left join coss_operation d4
on c.OprtIdent = d4.ident AND d4.UsrDfnId like 'HT%' 
left join coss_operation d5
on c.OprtIdent = d5.ident AND d5.UsrDfnId = 'QTC-TB' 
left join coss_operation d6
on c.OprtIdent = d6.ident AND (d6.UsrDfnId like 'TREPAN%' OR d6.UsrDfnId = 'QTC-TREPAN')
left join coss_operation d7
on c.OprtIdent = d7.ident AND (d7.UsrDfnId like 'OSP-%' OR d7.Description like 'External %' )
where a.createtime > '2015-06-01'
order by a.createtime, a.UsrDfnId

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Is repeating for how many rows you have in coss_operation table for that specific record.
Mystical_IceAuthor Commented:
chaau - Option 1 worked.  I tried Option 2 but got the same exact records as my query (with the same duplicates).

Good news is option 1 worked.  Thanks again for the help everyone :)
Mystical_IceAuthor Commented:
Option 1 worked in this case
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.