Search code examples
sqlsql-servert-sqlmultiple-value

SQL query returning values outside the specified range

I have written a SQL query to return a DocumentID when a specific column from the same table has 4 defined values. The problem I have is that I need to define IDs from another column for each value to be sure I have an accurate return.

The query I have written is;

SELECT DISTINCT 
    VV.[DocumentID], [Filename], [Path], D.LatestRevisionNo
FROM
    Documents D 
INNER JOIN
    [dbo].[DocumentsInProjects] DP ON DP.DocumentID = D.DocumentID 
INNER JOIN
    [dbo].[Projects] P ON DP.ProjectID = P.ProjectID 
INNER JOIN
    [dbo].[VariableValue] VV ON VV.DocumentID = D.DocumentID
WHERE 
    VV.ValueText IN ('170', '316', '2.125', 'LOCK RING') 
GROUP BY 
    VV.DocumentID, D.Filename, P.Path, D.LatestRevisionNo
HAVING 
    COUNT(DISTINCT ValueText) = 4 
    AND Filename LIKE '%sld%'

What I need is something like 

(VV.variableID = 79 AND VV.ValueText = 'LOCK RING') 
AND (VV.variableID = 92 AND VV.ValueText = '316') 
AND (VV.variableID = 328 AND VV.ValueText = '2.125') 
AND (VV.variableID = 351 AND VV.ValueText = '170')
Solution

  • You were close:

    (VV.variableID = 79 AND VV.ValueText = 'LOCK RING') 
OR (VV.variableID = 92 AND VV.ValueText = '316') 
OR (VV.variableID = 328 AND VV.ValueText = '2.125') 
OR (VV.variableID = 351 AND VV.ValueText = '170')

    variableId can't be 79 and 92 and 328 and 351 all on the same record. Thus or's are needed.