Querying a table with a composite primary key

I have a table with composite primary keys. This is a cross reference table and looks like this

Table 
ID    Relationship             ID2
1     Spouse                   10
2     Employee                 20
2     Former Employee          20
3     Former Employee          30
4   开发者_开发问答  Child                    40

I am trying to write a query where ID2 matches, you have both employee and former employee for same ID

ID    Relationship             ID2
2     Employee                 20
2     Former Employee          20      

Appreciate any help!!

In Oracle, SQL Server and PostgreSQL:

SELECT  *
FROM    (
        SELECT  t.*,
                COUNT(*) OVER (PARTITION BY id, id2) AS cnt
        FROM    mytable t
        WHERE   relationship IN ('Employee', 'Former Employee')
        ) q
WHERE   cnt = 2

Cross-platform version:

SELECT  t.*
FROM    (
        SELECT  id, id2
        FROM    mytable
        WHERE   relationship IN ('Employee', 'Former Employee')
        GROUP BY
                id, id2
        HAVING  COUNT(*) = 2
        ) q
JOIN    mytable t
ON      t.id = q.id
        AND t.id2 = q.id2
        AND t.relationship IN ('Employee', 'Former Employee')

select * from table where ID2 in (
   select ID2 from table where count(ID2) > 1) t
order by ID2