People with two relations

0

Please login or register to vote for this query.

(click on this box to dismiss)

http://stackoverflow.com/questions/5209873/sql-query-help-needed

Stack Overflow

Q&A for professional and enthusiast programmers

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

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results