Select value from multiple values in sql server 2008

Question

DECLARE @ProductFeature TABLE (ProductID int, FeatureID int)

INSERT INTO @ProductFeature
  SELECT 1,100
    UNION ALL
  SELECT 1,101
    UNION ALL
  SELECT 1,102
    UNION ALL
  SELECT 2,103
    UNION ALL
  SELECT 2,104
    UNION ALL
  SELECT 3,100
    UNION ALL
  SELECT 3,101
    UNION ALL
  SELECT 3,102
    UNION ALL
  SELECT 4,102
    UNION ALL
  SELECT 4,101
    UNION ALL
  SELECT 5,110
    UNION ALL
  SELECT 5,100
    UNION ALL
  SELECT 5,101

My requirement is if I pass ProductID = 1, then I have to select Product with features similiar to ProductID = 1.

Since ProductID = 1 has 3 features(100,101,102), there is only ProductID = 3 which has same count and features which has ProductID = 1

Expected result

ProductID   FeatureID
3              100
3              101
3              102

Oleksandr Fedorenko · Accepted Answer · 2013-01-28 19:31:20Z

2

Option with EXCEPT operation

DECLARE @ProductID int = 1

SELECT ProductID, FeatureID
FROM ProductFeature p1
WHERE p1.ProductID != @ProductID AND 
      NOT EXISTS (
                  SELECT p2.FeatureID         
                  FROM ProductFeature p2
                  WHERE p2.ProductID = @ProductID                                                
                  EXCEPT
                  SELECT p3.FeatureID
                  FROM ProductFeature p3
                  WHERE p3.ProductID = p1.ProductID               
                  )

edited Jan 28, 2013 at 19:31

answered Jan 28, 2013 at 12:33

Oleksandr Fedorenko

17k6 gold badges43 silver badges51 bronze badges

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

2 Comments

Andriy M Over a year ago

Interesting, although you don't need the AND p3.ProductID != @ProductID bit, because you are matching p3.ProductID with p1.ProductID and you've already specified p1.ProductID != @ProductID in the main query.

Oleksandr Fedorenko Over a year ago

thx @Andriy M you're right this condition excessive. BTW this pattern will be even more interesting if to involve indexes.

Nikshep · Accepted Answer · 2013-01-28 07:17:18Z

1

It is a bit in efficient but it work

select pr.ProductID , pr.FeatureID
from @ProductFeature pr
where pr.ProductID in (
    select prd.ProductID
    from @ProductFeature pr
    join @ProductFeature prd
    on pr.ProductID != prd.ProductID
    and pr.FeatureID = prd.FeatureID
    where pr.ProductID = @ProductId
    group by prd.ProductID
    having count(prd.ProductID) = (select count(distinct pr.FeatureID) from @ProductFeature pr where pr.ProductID = @ProductId)
    )

answered Jan 28, 2013 at 7:17

Nikshep

2,1254 gold badges22 silver badges31 bronze badges

Comments

lamLam · Accepted Answer · 2013-01-28 07:52:24Z

Normally i use cte -bit clearer (don't know if slower/faster).

    DECLARE @fromProductID int = 1;

    with cteGroup (ProductID) AS
     (
        select ProductID
        from @ProductFeature
        where FeatureID in (select FeatureID
                        from @ProductFeature 
                        where ProductID = @fromProductID)
           and ProductID <> @fromProductID
        group by ProductID
        having COUNT(FeatureID)= (select COUNT(FeatureID) as NoOfRecords
                                    from @ProductFeature 
                                  where ProductID = @fromProductID) 
    ) 
     select a.ProductID,b.FeatureID
     from cteGroup a
     inner join @ProductFeature b
     on a.ProductID = b.ProductID

Jacco · Accepted Answer · 2013-01-28 20:09:25Z

You'll first have to determine the products sharing at least one feature. Then from these products, find the one(s) that have exactly the same number of features.

This should do the trick:

DECLARE @productID int = 1

SELECT
  [p3].[ProductID],
  [p3].[FeatureID]
FROM
(
  SELECT 
    [p1].[ProductID]
  FROM [ProductFeature] [p1]
  INNER JOIN [ProductFeature] [p2] ON [p1].[FeatureID] = [p2].[FeatureID]
  WHERE [p1].[ProductID] <> [p2].[ProductID] AND [p2].[ProductID] = @productID
) AS [sub]
INNER JOIN [ProductFeature] [p3] ON [sub].[ProductID] = [p3].[ProductID]
GROUP BY
  [p3].[ProductID],
  [p3].[FeatureID]
HAVING COUNT(*) = (SELECT COUNT(*)
                   FROM [ProductFeature]
                   WHERE [ProductID] = @productID)
ORDER BY 
  [p3].[ProductID] ASC,
  [p3].[FeatureID] ASC

Look here for a Fiddle.

Collectives™ on Stack Overflow

Select value from multiple values in sql server 2008

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related