CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Chamz Des
Chamz Des

Reputation: 183

SQL Server - Selecting rows with values in two different ranges for the same column

I have two tables named 'Item' (with columns ItemId and Title) and 'ItemSpecificationValue' (with columns ItemId, ItemSpecificationValueId). 

    (Item Table)
 ItemId   |  Title                         
   1      |  abcd          
   2      |  pqrs          
   3      |  uvwx                             

(ItemSpecificationValue Table)
 ItemId   | ItemSpecificationValueId
   1      |  11
   2      |  50  
   2      |  55
   2      |  115
   3      |  11
   3      |  163

Say, I have needed to extract all rows from 'Item' table, which has at least one ItemSpecificationValueId from rangeA: {50,55} and at least one from rangeB: {11,115,163}. So you see the only item that satisfy above requirement is the one with ItemId 2.

My work so far is as below. I want to know if there is any easy way to do it, other than taking the union, because I could be getting a large number of those ranges so it is unfeasible to do large number of right joins. I could keep the result of the right join on a temporary table. Just wondering if there is a smarter way.

SELECT Item.ItemId, Item.Title
FROM Item 
    RIGHT JOIN ItemSpecificationValue
        ON ItemSpecificationValue.ItemId = Item.ItemId
WHERE ItemSpecificationValue.ItemSpecificationValueId in ('50','55')
INTERSECT 
SELECT Item.ItemId, Item.Title
FROM Item 
    RIGHT JOIN ItemSpecificationValue
        ON ItemSpecificationValue.ItemId = Item.ItemId
WHERE ItemSpecificationValue.ItemSpecificationValueId in ('11','115','163')

Upvotes: 2

Views: 834

Answers (1)

sgeddes
sgeddes

Reputation: 62841

This should work using COUNT with CASE:

select I.ItemId, I.Title
from Item I
  inner join ItemSpecificationValue ISV 
    on I.ItemId = ISV.ItemId
group by I.ItemId, I.Title
having count(case when ItemSpecificationValueId IN (50,55) then 1 end) > 0
   and count(case when ItemSpecificationValueId IN (11,115,163) then 1 end) > 0

Upvotes: 2

Related Questions