0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those committed to a vegan or vegetarian lifestyle and anyone interested in learning more

-- Matching properties

declare @PropertyValues table 
(
  PropertyID int, 
  PropertyValue int, 
  EntityID int
)

-- EntityID = 1, the source with three rows
insert into @PropertyValues values
(1, 1, 1),
(2, 2, 1),
(3, 3, 1)

-- EntityID = 2, the only match
insert into @PropertyValues values
(1, 1, 2),
(2, 2, 2),
(3, 3, 2)

-- EntityID = 3, matching but one property extra
insert into @PropertyValues values
(1, 1, 3),
(2, 2, 3),
(3, 3, 3),
(4, 4, 3)

-- EntityID = 4, one property missing
insert into @PropertyValues values
(1, 1, 4),
(2, 2, 4)

-- EntityID = 5, matching properties different values
insert into @PropertyValues values
(1, 10, 5),
(2, 20, 5),
(3, 30, 5)

-- EntityID = 6, matching values different properties
insert into @PropertyValues values
(10, 1, 6),
(20, 2, 6),
(30, 3, 6)


declare @EntityID int = 1


;with cteSource as
(
  select PropertyId,
         PropertyValue
  from @PropertyValues
  where EntityId = @EntityID
)
select PV.EntityId
from @PropertyValues as PV
  inner join cteSource as S  
    on PV.PropertyId = S.PropertyId and
       PV.PropertyValue = S.PropertyValue and
       PV.EntityId <> @EntityID
group by PV.EntityId
having count(*) = (select count(*)
                   from cteSource) and
       count(*) = (select count(*)
                   from @PropertyValues as PV1
                   where PV1.EntityId = PV.EntityId)

fork query

created aug 23 11 Mikael Eriksson