sql find uniq row exclude different duplicate row

Question

I have a table with:

id  field_id    todelete
1   x001        0
2   x001        1
3   x001        0
4   x002        1
5   x003        0
6   x003        1
7   x004        0
8   x005        0

From this I need id 4, it has todelete = 1 and its unique in field_id. My current solution:

array = SELECT field_id WHERE todelete = 1 

and from that array

SELECT field_id WHERE field_id = array[x] HAVING COUNT(field_id) = 1

Seems to be the wrong solution. Can I have this in SQL only way? Thanks

Answer 1

You were on the good path. There is certainly other solutions but those are 2 that work as you want :

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE Table1
    (`id` int, `field_id` varchar(4), `todelete` int)
;

INSERT INTO Table1
    (`id`, `field_id`, `todelete`)
VALUES
    (1, 'x001', 0),
    (2, 'x001', 1),
    (3, 'x001', 0),
    (4, 'x002', 1),
    (5, 'x003', 0),
    (6, 'x003', 1),
    (7, 'x004', 0),
    (8, 'x005', 0)
;

Query 1:

SELECT id 
FROM Table1
WHERE field_id in 
    (SELECT field_id
     FROM Table1
     WHERE todelete = 1)
GROUP BY field_id
HAVING count(*)=1

Results:

| id |
|----|
|  4 |

Query 2:

SELECT id 
FROM Table1
WHERE todelete = 1
AND field_id in 
    (SELECT field_id
     FROM Table1
     GROUP BY field_id
     HAVING count(*)=1)

Results:

| id |
|----|
|  4 |