CODEWITHSUNDEEP
sqlsql-server-2008
SQL006
SQL006

Reputation: 492

Select value from multiple values in sql server 2008

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

Upvotes: 2

Views: 1791

Answers (4)

Jacco
Jacco

Reputation: 3271

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.

Upvotes: 1

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16894

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               
                  )

Upvotes: 2

lamLam
lamLam

Reputation: 475

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

Upvotes: 1

Nikshep
Nikshep

Reputation: 2115

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)
    )

Upvotes: 1

Related Questions