Determine if two fields are equal

Question

I'm trying to determine if one field (Approved) in one row in my table equals the Approved field in another row of the same table. There will always be only two records per each CommonId. I need to have a scalar return value of 1 indicating the two Approved fields are equal or a 0 indicating that the two Approved fields are not equal. Apparently I do not understand what the query is that I need.

Here is a representation of my table structure.

RecordId int
CommonId int
Approved bit

Records In the two records below, I would like to get a 0 returned indicating that the Approved fields are not equal.

RecordId = 1
CommonId = 5
Approved = 1

RecordId = 2
CommonId = 5
Approved = 0

In the next two records below, I would like to get a 1 returned indicating that the Approved fields are equal.

RecordId = 3 
CommonId = 9
Approved = 1

RecordId = 4
CommonId = 9
Approved = 1

This is the query I've tried, but I get two rows and does not indicate if the two fields are equal.

SELECT A.Approved
FROM MyTable A
INNER JOIN MyTable B ON A.Approved = B.Approved
WHERE A.CommonId = 5

I tried using a GROUP BY to see if I could get just one row, but that does not work either.

SELECT Approved
FROM MyTable A
INNER JOIN MyTable B ON A.Approved = B.Approved
WHERE A.CommonId = 5
GROUP BY B.CommonId

Can someone show me the correct query to get what I want? Thank you.

Answer 1

Perhaps a group by would help?

select commonId,
       (case when max(Approved) = min(Approved) then 1 else 0 end) as IsEqual
from MyTable A
group by commonId;

This formulation assumes that the values are not NULL in the approved column (your question doesn't mention this as a possibility).

Answer 2

If I understand what you are looking for correctly

SELECT 
    CASE 
        WHEN (A.Approved = B.Approved) THEN 1
        ELSE 0
    END
FROM MyTable A,
MyTable B
WHERE A.commonId = B.commonId