CODEWITHSUNDEEP
sqlmariadb
Patryk Tchórzewski
Patryk Tchórzewski

Reputation: 127

How to select unique value from two columns if third have the same value

TABLE:

id second_id status
1  2         1
2  1         0
1  3         1
3  1         1
1  4         1
4  1         1

I try to select only this unique value where the pair have the same status OUTPUT: 

id second_id status
1  3         1
1  4         1

I try with

SELECT table.id, table.second_id, table.status FROM table 
WHERE table.id = 1 and table.status = 1

but this return of course bad results ;) Thx for help :)

Upvotes: 0

Views: 27

Answers (2)

Nick
Nick

Reputation: 147166

One way to do this is to JOIN the table to itself, looking for matching id and second_id values with the same status. We also check that the second table's id value is greater than the first to avoid duplicates:

SELECT t1.*
FROM test t1
JOIN test t2 ON t2.id = t1.second_id
            AND t2.second_id = t1.id
            AND t2.status = t1.status
            AND t2.id > t1.id

Output:

id  second_id   status
1   3           1
1   4           1

Demo on dbfiddle

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269753

One method uses exists:

select t.*
from t
where exists (select 1
              from t t2
              where t2.second_id = t.id and
                    t2.id = t.second_id and
                    t2.status = t.status
             );

Or, expressing this using in and tuples is simpler:

select t.*
from t
where (id, second_id, status) in
          (select second_id, id, status from t);

Upvotes: 0

Related Questions