Finding contradiction in mysql table

Question

I have a table of 5000 rows with 9 columns in it. And I am in a process of data cleaning. So I need a query to return only rockets names that are Active and Retired at the same time

Below is a sample of 2 columns that I am working on :

Rocket	Status
Sputnik	Retired
Sputnik	Active
Vanguard	Retired
Juno I	Retired
Sputnik	Retired
Vostok	Retired

So the result should be like this :

Rocket	Status
Sputnik	Retired
Sputnik	Active

I tried distinct, self join, group by but I failed to achieve my goal.

-- This query will return every distinct rows:

select distinct(concat(rocket,'_', rocketstatus)) as BB

from space.test_1

group by bb

-- This query will return nothing:

select a.rocket, a.rocketstatus from space.test_1 b

join space.test_1 a on a.id = b.id

where a.rocketstatus not in (select b.rocketstatus from space.test_1 b)

Answer 1

May be this variant will suit you

select distinct a.Rocket,b.Status from 
    (
      select Rocket,count(distinct Status) as cnt from test_1 group by Rocket
    ) a inner join test_1 b on b.Rocket=a.Rocket where a.cnt>1