declare @YourTable table (name_1 varchar(30), name_2 varc...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow Meta en español

Preguntas y respuestas sobre el sitio para programadores y profesionales de la informática

declare @YourTable table (name_1 varchar(30), name_2 varchar(30), rel varchar(10));
insert  @YourTable (name_1, name_2, rel) values 
    ('Lady', 'Tramp', 'coworker'),
    ('Lady', 'Tramp', 'friend'),
    ('Lady', 'Jock', 'coworker'),
    ('Lady', 'Jock', 'enemy');

select  name_1
,       name_2
from    (
        select  case when name_1 > name_2 then name_1 else name_2 end as name_1
        ,       case when name_1 > name_2 then name_2 else name_1 end as name_2
        ,       case when rel like 'c%' then 1 
                     when rel like 'f%' then 2 
                end as Type
        from    @YourTable
        ) as SubQueryAlias
group by
        name_1
,       name_2
having  count(distinct Type) = 2


select  c.name_1
,       c.name_2
from    @YourTable c
where   c.rel like 'c%'
        and exists
        (
        select  *
        from    @YourTable f
        where   f.rel like 'f%'
                and 
                (
                    (c.name_1 = f.name_1 and c.name_2 = f.name_2)
                    or (c.name_1 = f.name_2 and c.name_2 = f.name_1)
                )
        )

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results