CODEWITHSUNDEEP
mysqlsqldatabasegroup-by
Moyed Ansari
Moyed Ansari

Reputation: 8461

Mysql query help required using GROUP BY?

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 ?

Upvotes: 3

Views: 112

Answers (4)

Konerak
Konerak

Reputation: 39763

You should use a NOT EXISTS

Select referral_id,  status
FROM yourtable a
WHERE NOT EXISTS (
  select 1 
  from yourtable b 
  where b.referral_id = a.referral_id 
  and b.status != 3)

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

Start with this

select referral_id from table
group by referral_id 
having (min(status)=3 and max(status)=3 and count(status)=3) or (count(status)<3)

Upvotes: 0

Doctor Chris Chris
Doctor Chris Chris

Reputation: 211

what about ...

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

Upvotes: 6

aF.
aF.

Reputation: 66697

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

Upvotes: 4

Related Questions