SQL Server - WHERE Clauses for column dynamic

Question

I have the following table,

id    idAttribute   valueAttribute    idPiece
1         9              '2048'         10
2        18              'DDR3'         10
3         9              '2048'         11
4        18              'DDR3'         12

When doing this query:

SELECT * 
FROM tb_inventary_report 
WHERE (idAtribute = 9 AND valueAtribute = '2048') 
OR (idAtribute = 18 AND valueAtribute = 'DDR3')

I am returning records 1, 2, 3 and 4, however I want to return only 1 and 2 because they have the same idPiece, but I can not inform the idPiece, ie, idPiece has to be Iqual between the ORs that i pass

How can I do to return only the 1 and 2?

I'm not sure the question... are you asking if your query is valid? Because it is... — Siyual
– Siyual, Commented Aug 22, 2017 at 18:32

SqlZim · Accepted Answer · 2017-08-22 18:37:13Z

1

using exists() with an aggregation query to count(*) rows that match your conditions, having count(*) = the number of conditions you need to match.

select * 
from tb_inventary_report as t
where exists (
  select 1
  from tb_inventary_report as i
  where i.idPiece = t.idPiece
    and (
         (idAttribute = 9  and valueAttribute = '2048') 
      or (idAttribute = 18 and valueAttribute = 'ddr3')
      )
  group by i.idPiece
  having count(*) = 2
  )

rextester demo: http://rextester.com/TKXCJ68213

returns:

+----+-------------+----------------+---------+
| id | idattribute | valueattribute | idpiece |
+----+-------------+----------------+---------+
|  1 |           9 | 2048           |      10 |
|  2 |          18 | ddr3           |      10 |
+----+-------------+----------------+---------+

answered Aug 22, 2017 at 18:37

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eli · Accepted Answer · 2017-08-22 18:37:39Z

If I understood your question correctly, you would want to have two pieces in your WHERE clause; a value for the idPiece, and either of the choices which you currently have in the WHERE clause. That being said, you can do as follows:

SELECT 
    * 
FROM tb_inventary_report 
WHERE idPiece = <insert value here.
AND( (idAtribute = 9 AND valueAtribute = '2048') 
OR (idAtribute = 18 AND valueAtribute = 'DDR3'))

Explanation: If you wrap your current version in a set of parenthesis it will evaluate all which is in there as one condition. What many stumble upon is omitting this parenthesis, which gives you very different results; namely, it will evaluate for the combination of the first two conditions (AND), OR if the third condition alone is true.

FuzzyTree · Accepted Answer · 2017-08-22 18:37:51Z

If you want to select all idPiece values that have both idAttribute, valueAttribute pairs then you can group by idPiece and count the number of distinct idAttributes:

SELECT idPiece 
FROM tb_inventary_report 
WHERE (idAtribute = 9 AND valueAtribute = '2048') 
OR (idAtribute = 18 AND valueAtribute = 'DDR3')
GROUP BY idPiece
HAVING COUNT(distinct idAttribute) > 1

If you want to select the entire row, then you can put the query above in a derived table

SELECT * FROM tb_inventary_report t1
JOIN (
    SELECT idPiece 
    FROM tb_inventary_report 
    WHERE (idAtribute = 9 AND valueAtribute = '2048') 
    OR (idAtribute = 18 AND valueAtribute = 'DDR3')
    GROUP BY idPiece
    HAVING COUNT(distinct idAttribute) > 1
) t2 on t2.idPiece = t1.idPiece
WHERE (t1.idAtribute = 9 AND t1.valueAtribute = '2048') 
OR (t1.idAtribute = 18 AND t1.valueAtribute = 'DDR3')

Collectives™ on Stack Overflow

SQL Server - WHERE Clauses for column dynamic

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related