CODEWITHSUNDEEP
mysql
karlosuccess
karlosuccess

Reputation: 885

Get records GROUPing BY and discarding duplicates

I have a "transaction_status" table:

id  transaction_id  status
1         12           0
2         13           0
3         12          -1
4         14           0
5         13          -1
6         15           0
[END OF TABLE]

I need to get the transaction ids which only have a record with status=0.

For example this is what I am looking for from the above table:

id   transaction_id   status
4          14            0
6          15            0

So far I am trying:

SELECT id, transaction_id, status  
FROM transaction_status
WHERE status = 0 OR status = -1
ORDER BY status ASC
GROUP BY txn_id
HAVING status = 0

Upvotes: 0

Views: 45

Answers (3)

Akhil
Akhil

Reputation: 2602

Made a small change in the having clause.

SELECT id, transaction_id, status  
FROM transaction_status
WHERE status = 0 OR status = -1
GROUP BY transaction_id
HAVING min(status) = 0

I also removed order by as it does not make any sense

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

I think the easiest way to handle this is to use conditional aggregation by transaction_id and count which IDs have only a status of 0 associated with them. Do this in a subquery, and then join back to your main table to retrieve the full matching records which you want.

SELECT t1.*
FROM transaction_status t1
INNER JOIN
(
    SELECT transaction_id
    FROM transaction_status
    GROUP BY transaction_id
    HAVING SUM(CASE WHEN status <> 0 THEN 1 ELSE 0 END) = 0
) t2
    ON t1.transaction_id = t2.transaction_id
ORDER BY t1.id DESC

Upvotes: 0

Ullas
Ullas

Reputation: 11556

Use GROUP BY and in having clause check for both min and max status is 0.

Query

select  `transaction_id`
from `your_table_name`
group by`transaction_id`
having min(`status`) = 0 and max(`status`) = 0;

Upvotes: 1

Related Questions