CODEWITHSUNDEEP
mysqlsql
Vitaly Mirsky
Vitaly Mirsky

Reputation: 77

MySQL query to find unique records

I have the following table:

+----+-------+---------+
| id | media | status  |
+----+-------+---------+
|  1 | FOO   | ACTIVE  |
|  1 | FOO   | PENDING |
| >1 | BAR   | ACTIVE  |
|  2 | FOO   | ACTIVE  |
|  2 | FOO   | PENDING |
| >3 | BAR   | ACTIVE  |
+----+-------+---------+

What I need to get is a list of id, which have ACTIVE records with certain media, but have no PENDING record with the same media. In my example id=1 have covered FOO, but uncovered BAR.

So the resulting table should be:

+----+
| id |
+----+
|  1 |
|  3 |
+----+

The only solution I see is to create two tables with ACTIVE and PENDING records separately, then find records, which are only in ACTIVE and not in PENDING. But I have no idea how to construct the request.

Please advise.

Upvotes: 1

Views: 75

Answers (5)

sumit
sumit

Reputation: 15464

GROUP CONCAT

select id from tbl group by media,id 
having group_concat(status)='active'

Upvotes: 0

shmosel
shmosel

Reputation: 50756

SELECT DISTINCT id
  FROM t
 GROUP BY id, media
HAVING MAX(status) = 'ACTIVE';

This assumes that an id that has a pending media should still be included if it also has a different media type without pending. If that's incorrect, or not applicable, you can remove DISTINCT from the SELECT and media from the GROUP BY.

Upvotes: 1

Kickstart
Kickstart

Reputation: 21533

Using a LEFT JOIN:-

SELECT a.id
FROM some_table a
LEFT OUTER JOIN some_table b
ON a.id = b.id
AND a.media = b.media
AND a.status = 'ACTIVE'
AND b.status = 'PENDING'
WHERE b.id IS NULL

This self joins the table, one for active and one for pending. Checks that b.id IS NULL to check there is no match.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270873

I would do this with group by and having, because this is a very flexible way to formulate this type of query:

select id
from table t
group by id, media
having sum(status = 'ACTIVE') > 0 and
       sum(status = 'PENDING') = 0;

Upvotes: 1

Raphaël Althaus
Raphaël Althaus

Reputation: 60503

You can use a NOT EXISTS clause (a NOT IN would also work).

select distinct id
from table t
where status = 'ACTIVE'
and not exists (select null
                  from table
                  where t.id = id and t.media = media
                  and status = 'PENDING')

see sqlFiddle

Upvotes: 0

Related Questions