I would like to select unique records using childkey field. the resultset should not include any record with repeated childkey.
IF My Data looks like
ParentKey ChildKey
---------- ---------
PARENT1 CHILD1
PARENT1 CHILD2
PARENT2 CHILD1
Select Statement Result should be
ParentKey ChildKey
---------- ---------
PARENT1 CHILD1
PARENT1 CHILD2
HERE is the SP.
WITH KIDD (ParentKey, ChildKey)
AS
(
select KI_SLDrillDown.ParentKey, KI_SLDrillDown.ChildKey
from KI_SLDrillDown
where KI_SLDrillDown.ChildKey = ('O_123456' )
and KI_SLDrillDown.ParentKey is NULL
union ALL
select KI_SLDrillDown.ParentKey, KI_SLDrillDown.ChildKey from KI_SLDrillDown
inner join KIDD as d
on KI_SLDrillDown.ParentKey = d.ChildKey
)
SELECT ParentKey, ChildKey