sql server select query one column is the same and another is different

Question

I want to be able to find occurrences in a table where one column is the same but another is different.

Example table:

id    team    week
1     1       1
1     2       1
2     2       1
2     1       2

I want a query that will find all ids where team is different yet week is still the same so something like team does not equal team, but week does equal week.

Basically I would like to know if any id changed teams in the same week, how do i do this?

Answer 1

SELECT 
    t1.id, 
    t1.week
FROM 
    YourTable t1
    JOIN YourTable t2 
        ON t1.ID = t2.ID
        AND t1.team < t2.team
        AND t1.week = t2.week