Mysql query help required using GROUP BY?

Question

I have a table with 2 columns, following is my table structure

referral_id | status

531 | 0
531 | 0
531 | 3
530 | 3
529 | 3
528 | 3
527 | 3
527 | 0
527 | 0
523 | 2
523 | 0
523 | 3
522 | 3
522 | 3
522 | 3
522 | 3
511 | 3

My expected output is

referral_id | status

530 | 3
529 | 3
528 | 3
522 | 3
511 | 3

The 1st column referral_id can have multiple tuples with same id (see referral_id's 531 and 527). I need to make sure that tuples with same referral_id get eliminated if all of their corresponding "status" are NOT 3. If all of the corresponding "status" are 3 then I need to apply GROUP BY to get that referral_id(s) in result. "status" column can have values from 1 to 4, but I just need to look for 3. So how can I achieve this result ?

Answer 1

what about ...

select referral_id, max(status)
from tablename
group by referral_id
having max(status) = 3 and max(status) = min(status)

Answer 2

You don't need to group by, just do it like this:

select distinct t.referral_id
from tablename t
where t.referral_id not in (
                            select referral_id
                            from tablename
                            where status <> 3
                           )
order by t.referral_id desc