Hi,
Have a speciification table and needed products which is Colored White and Shape is either round or rectangle. Tried with a self join query,would like to know if any other method to yield the expected result .Using sql 2012 version.
Declare @specTable table(prodID int,prodName varchar(30),specName varchar(100), specValue varchar(100))
Insert into @specTable values
(1,'Sink1','Shape','Round')
,(1,'Sink1','Color','White')
,(1,'Sink1','Style','Pedestal')
,(2,'Sink2','Shape','Square')
,(2,'Sink2','Color','Black')
,(2,'Sink2','Style','Wallmount')
,(3,'Sink3','Shape','Round')
,(3,'Sink3','Color','White')
,(3,'Sink3','Style','Pedestal')
,(4,'Sink4','Shape','Rectangle')
,(4,'Sink4','Color','White')
,(4,'Sink4','Style','Pedestal')
,(5,'Sink5','Shape','Round')
,(5,'Sink5','Color','green')
,(5,'Sink5','Style','Pedestal')
SELECT
T1.prodID
FROM @specTable T1
INNER JOIN @specTable T2 ON T1.prodID = T2.prodID
WHERE
( t1.SpecValue IN ('White')
)
AND
( t2.SpecValue IN ('Round','Rectangle')
)
group by T1.prodID
Select * from @specTable
Expected result: Prod1,Prod3,Prod4