CODEWITHSUNDEEP
mysql
user3255066
user3255066

Reputation: 41

MySQL SELECT distinct with multiple similar rows

I am struggling to get the correct data returned, I have a table with the following data structure:

# order_id, name
'10434', 'docs_sent'
'10433', 'placed'
'10433', 'docs_sent'
'10433', 'agreement_received'
'10432', 'placed'
'10431', 'placed'
'10431', 'docs_sent'
'10430', 'placed'
'10430', 'docs_sent'
'10430', 'agreement_received'
'10429', 'placed'
'10429', 'docs_sent'
'10429', 'agreement_received'
'10428', 'placed'
'10428', 'docs_sent'
'10427', 'placed'

What I am trying to do is return a unique "order_id" where "name" does not include agreement_received. So to clarify I am looking for a list of order numbers "order_id" where the order number does not have any record with agreement_received". The issue is that each order has multiple entries and I cant seem to get it right. PLEASE HELP!

The output should be similar to:

# order_id
'10434'
'10432'
'10431'
'10428'
'10427'

Upvotes: 1

Views: 36

Answers (4)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

You want to group by order_id and only keep those order_ids having no 'agreement_received' record.

select order_id
from mytable
group by order_id
having sum(name = 'agreement_received') = 0;

(This uses MySQL's special boolean treatment. False = 0, True = 1. In other dbms you would have to use sum(case when name = 'agreement_received' then 1 else 0 end).)

Upvotes: 1

mhall
mhall

Reputation: 3701

If your table name is t:

select distinct t1.order_id
from t as t1
where t1.order_id not in
   (select t2.order_id
    from t as t2
    where t2.name = 'agreement_received')

Output:

+----------+
| order_id |
+----------+
|    10434 |
|    10432 |
|    10431 |
|    10428 |
|    10427 |
+----------+

Upvotes: 0

SMA
SMA

Reputation: 37023

Try something like:

SELECT order_id
FROM MyTable
WHERE name <> 'agreement_received'
GROUP BY order_id
HAVING COUNT(order_id) = 1

Upvotes: 0

Jerry Andrews
Jerry Andrews

Reputation: 847

select distinct order_id
from orders
where name != 'agreement_received';

Does that work for you?

Upvotes: 0

Related Questions