SQL: Selecting IDs that don't have any rows with a certain value for a column

php, sql

Solution

SELECT DISTINCT id
FROM table
WHERE id NOT IN (SELECT id
                 FROM table
                 WHERE val = 'current')

or:

SELECT a.id
FROM table a
LEFT JOIN table b ON a.id = b.id AND b.val = 'current'
WHERE b.id IS NULL

Problem

I want to select the distinct IDs (which is associated with multiple rows) that DOESN'T have any rows where VAL = 'current'. For example, in a table like this: ``` PK | ID | VAL ------------- 1 | 23 | deleted 2 | 23 | deleted 3 | 23 | deleted 4 | 45 | current 5 | 45 | deleted 6 | 45 | deleted ...|................ ``` I would want it to return ID 23, because it has no rows where VAL='current'. Note that in this table, the primary keys (PK) are unique, but IDs are not (hence the need to use DISTINCT or GROUP BY). Here is what I have in PHP: ``` $conn = someConnect(""); // returns ids associated with the number of rows they have where VAL != 'current' $sql = "SELECT id, count(*) FROM table WHERE val != 'current' GROUP BY id" $stid = oci_parse($conn, $sql); oci_execute($stid); oci_fetch_all($stid, $arr, OCI_FETCHSTATEMENT_BY_ROW); foreach ($arr as $elm) { $id = key($elm); $non_current_count = $elm[$id]; // counts the number of rows associated with the id, which includes VAL = 'current' rows $sql2 = "SELECT count(*) FROM table WHERE id = $id"; $stid2 = oci_parse($conn, $sql2); oci_execute($stid2); $total_count = oci_fetch_array... if ($total_count != $non_current_count) { $output[] = $id; } ... } oci_close($conn); ``` That's the general gist of it. As you can see, it takes two SQL statements to accomplish this task. Is there a shorter way of doing this?

Original source