CODEWITHSUNDEEP
sqlexists
S_241
S_241

Reputation: 23

Selecting rows where some columns are same but others are different

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?

Upvotes: 1

Views: 47

Answers (2)

Sergey
Sergey

Reputation: 5217

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

Upvotes: 1

Salman Arshad
Salman Arshad

Reputation: 272126

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
)

Upvotes: 2

Related Questions