select distinct main.PrimeId
from PRIME main
join
(
select distinct p.PrimeId   from PRIME p
left  outer join ATTRGROUP a
on p.PrimeId = a.PrimeId   or p.PrimeId = a.RelatedPrimeId
where a.PrimeId is not null and a.RelatedPrimeId is not null
) mem
on main.PrimeId = mem.PrimeId

select distinct main.PrimeId
from PRIME main
join
(
select distinct p.PrimeId   from PRIME p
left  outer join ATTRGROUP a
on p.PrimeId = a.PrimeId
where a.PrimeId is not null and a.RelatedPrimeId is not null
UNION
select distinct p.PrimeId   from PRIME p
left  outer join ATTRGROUP a
on p.PrimeId = a.RelatedPrimeId
where a.PrimeId is not null and a.RelatedPrimeId is not null
) mem
on main.PrimeId = mem.PrimeId

select distinct p.PrimeId into #temp
from PRIME p
left  outer join ATTRGROUP a
on p.PrimeId = a.PrimeId   or p.PrimeId = a.RelatedPrimeId
where a.PrimeId is not null and a.RelatedPrimeId is not null

select distinct main.PrimeId
from Prime main join
#temp mem
on main.PrimeId = mem.PrimeId

left  outer join ATTRGROUP a
    on p.PrimeId = a.PrimeId   or p.PrimeId = a.RelatedPrimeId

left  outer join ATTRGROUP a
    on p.PrimeId = a.PrimeId
    left  outer join ATTRGROUP a2
    on p.PrimeId = a2.RelatedPrimeId

select PrimeId   from ATTRGROUP a
union
select RelatedPrimeId from ATTRGROUP a

select distinct main.PrimeId
from PRIME main
join
(
select distinct p.PrimeId   from PRIME p
left  outer join ATTRGROUP a
on p.PrimeId = a.PrimeId
where *main.PrimeId = a.PrimeId*
UNION
select distinct p.PrimeId   from PRIME p
left  outer join ATTRGROUP a
on p.PrimeId = a.RelatedPrimeId
where *main.PrimeId = a.PrimeId*
) mem
on main.PrimeId = mem.PrimeId