Mysql query help required using GROUP BY?

Question

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

referral_id | status

My expected output is

referral_id | status

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 ?

Wait, are you sure you mean remove if ALL are not 3? Because since 527 has a 3, that would mean 527 would be in the results. Or do you mean remove if not all are 3? — Konerak
– Konerak, Commented Jul 5, 2012 at 12:30
@Konerak what I understood is that we need to show all referral_id's when all of the status for that referral_id are equal to 3. — aF.
– aF., Commented Jul 5, 2012 at 12:37
@Konerak yes, i need all referral_ids which dont have status 3 — Moyed Ansari
– Moyed Ansari, Commented Jul 5, 2012 at 13:06
@BugFinder I tried this SELECT a.referral_id FROM referral_practitioners a JOIN referral_practitioners b ON a.referral_id = b.referral_id AND b.status <> 3 — Moyed Ansari
– Moyed Ansari, Commented Jul 5, 2012 at 13:07

aF. · Accepted Answer · 2012-07-05 12:34:40Z

6

what about ...

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

edited Jul 5, 2012 at 12:34

aF.

67k45 gold badges141 silver badges201 bronze badges

answered Jul 5, 2012 at 12:32

Doctor Chris Chris

2111 gold badge3 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

aF. · Accepted Answer · 2012-07-05 12:30:05Z

4

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

answered Jul 5, 2012 at 12:30

aF.

67k45 gold badges141 silver badges201 bronze badges

Comments

Madhivanan · Accepted Answer · 2012-07-05 12:34:42Z

0

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)

answered Jul 5, 2012 at 12:34

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

1 Comment

aF. Over a year ago

why the count's? He doesn't need to be exactly 3 rows for each group.

Konerak · Accepted Answer · 2012-07-05 12:37:21Z

0

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)

edited Jul 5, 2012 at 12:37

answered Jul 5, 2012 at 12:31

Konerak

39.8k13 gold badges102 silver badges121 bronze badges

1 Comment

aF. Over a year ago

your second query is wrong. I think it'll ll select all data.

Collectives™ on Stack Overflow

Mysql query help required using GROUP BY?

4 Answers 4

Comments

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related