Isolating rows where some variables are equal but others are different-CodePudding

I'm new to SQL so I appreciate if this seems a bit basic.

I have a dataset set up as follows:

Name	Age	Job	Start	End
Joe	20	Plumber	01/20	08/20
Joe	20	Joiner	08/20	09/21
Kevin	28	Plumber	07/20	08/20
Kevin	28	Plumber	08/20	09/21
Michael	25	Plumber	01/20	11/20
Michael	25	Joiner	11/20	07/21

I'm trying to get all rows where the name and age are the same but the job differs. The output dataset should look like this:

Name	Age	Job	Start	End
Joe	20	Plumber	01/20	08/20
Joe	20	Joiner	08/20	09/21
Michael	25	Plumber	01/20	11/20
Michael	25	Joiner	11/20	07/21

Could someone be able to help me out?

CodePudding user response：

You can check if a duplicate (name, age) pair EXISTS but has different job:

SELECT *
FROM t AS t1
WHERE EXISTS (
    SELECT *
    FROM t AS t2
    WHERE t2.name = t1.name
    AND t2.age = t1.age
    AND t2.job <> t1.job
)

CodePudding user response：

SELECT C.*
  FROM YOUR_TABLE AS C
  JOIN YOUR_TABLE AS C2 ON C.Name=C2.Name AND C.Age=C2.Age AND C.Job<>C2.Job