AnsweredAssumed Answered

SQL Query Duplicate Values Help

Question asked by Sergejs Sinicins on Jun 7, 2019
Latest reply on Jun 12, 2019 by Sergejs Sinicins

Hello everybody!

 

I'm trying to get the data from EPDM into Excel via Query 1 (see below). It works (in a way) but for some reason it extracts duplicates values despite I'm using MAXVARS. 

 

Query 1

with maxvars as
(select documentid, MAX(RevisionNo) RevisionNo, configurationid, variableid
from VariableValue group by DocumentID, ConfigurationID, VariableID)

select DISTINCT d.filename, va.ValueText as [M-Number], va.ValueText as [Description], va.ValueText as [Customer SN], w.name as [Workflow] -- the names in [] are the column names returned below
from Documents d

inner join status s on s.StatusID = d.CurrentStatusID
inner join workflows w on w.WorkflowID = s.WorkflowID

inner join VariableValue va on va.DocumentID = d.DocumentID
inner join maxvars mv on mv.DocumentID = va.DocumentID and mv.RevisionNo = va.RevisionNo and mv.ConfigurationID = va.ConfigurationID --and mv.VariableID = va.VariableID
inner join variable v on v.variableid = va.variableid and v.VariableName = 'Description' --edit this Variable name to search for

inner join VariableValue vb on vb.DocumentID = d.DocumentID
inner join maxvars mv1 on mv1.DocumentID = vb.DocumentID and mv1.ConfigurationID = vb.ConfigurationID and mv1.RevisionNo = vb.RevisionNo and mv1.VariableID = vb.VariableID
inner join variable v1 on v1.variableid = vb.variableid and v1.VariableName = 'Number O-Code' --edit this Variable name to search for

inner join VariableValue vc on vc.DocumentID = d.DocumentID
inner join maxvars mv2 on mv2.DocumentID = vc.DocumentID and mv2.ConfigurationID = vc.ConfigurationID and mv2.RevisionNo = vc.RevisionNo and mv2.VariableID = vc.VariableID
inner join variable v2 on v2.variableid = vc.variableid and v2.VariableName = 'Number Customer' --edit this Variable name to search for

where d.deleted = 0
and w.Name = 'CAD Data' --Workflow name
and mv.configurationID = 2
and mv1.ConfigurationID = 2
and mv2.ConfigurationID = 2
and d.filename LIKE 'G-___________' --filename WITH extension
or d.filename LIKE 'M-___________' --filename WITH extension

ORDER BY D.Filename

 

There are no duplicate file names in the system. 

 

Duplicates_1

Query result:

Duplicates_2

 

When I've tried to add the LATEST version but it went from bad to worse: 

Duplicates_3

 

I just can't understand why is it doing it, and more important how to fix it.

 

The end result I want to see is just ONE line with data extracted from "@" configuration.

 

Thanks

Sergejs

Outcomes